[rt-devel] RT 4.2.7: SQL error on ticket traffic
Brian McNally
bmcnally at uw.edu
Fri Sep 26 16:50:11 EDT 2014
1. In terms of tables/sequences that are different: the problematic
instance has this, where the functional one does not.
rt3_admin=# \d
List of relations
Schema | Name | Type | Owner
--------+------------------------------+----------+----------------
public | objectcustomfieldvalues_id_s | sequence | rt3_admin_user
2. Should all objects in the database be owned by the RT database user?
I ask because in my functional copy most things are and a few things are
owned by postgres whereas in my non-functional instance most things are
owned by postgres. The owner of both databases is the RT database user.
3. In terms of indexes, the non-functional instance's database has many
fewer indexes. For example
rt3_admin=# \di
List of relations
Schema | Name | Type | Owner | Table
--------+---------------------+-------+----------+--------------------
public | articles_pkey | index | postgres | articles
public | cachedgroupmembers1 | index | postgres | cachedgroupmembers
public | cachedgroupmembers2 | index | postgres | cachedgroupmembers
public | cachedgroupmembers3 | index | postgres | cachedgroupmembers
public | classes_pkey | index | postgres | classes
public | contentindex_idx | index | postgres | attachments
public | groupmembers1 | index | postgres | groupmembers
public | groups1 | index | postgres | groups
public | groups2 | index | postgres | groups
public | groups3 | index | postgres | groups
public | objectclasses_pkey | index | postgres | objectclasses
public | objectscrips1 | index | postgres | objectscrips
public | objectscrips_pkey | index | postgres | objectscrips
public | objecttopics_pkey | index | postgres | objecttopics
public | queues1 | index | postgres | queues
public | topics_pkey | index | postgres | topics
public | users1 | index | postgres | users
(17 rows)
vs.
rt_gsits=# \di
List of relations
Schema | Name | Type | Owner |
Table
--------+------------------------------+-------+---------------+-------------------------
public | acl1 | index | rt_gsits_user | acl
public | acl_pkey | index | rt_gsits_user | acl
public | articles_pkey | index | postgres | articles
public | attachments1 | index | rt_gsits_user |
attachments
public | attachments2 | index | rt_gsits_user |
attachments
public | attachments3 | index | rt_gsits_user |
attachments
public | attachments_pkey | index | rt_gsits_user |
attachments
public | attributes1 | index | rt_gsits_user | attributes
public | attributes2 | index | rt_gsits_user | attributes
public | attributes_pkey | index | rt_gsits_user | attributes
public | cachedgroupmembers1 | index | rt_gsits_user |
cachedgroupmembers
public | cachedgroupmembers4 | index | rt_gsits_user |
cachedgroupmembers
public | cachedgroupmembers_pkey | index | rt_gsits_user |
cachedgroupmembers
public | classes_pkey | index | postgres | classes
public | contentindex_idx | index | rt_gsits_user |
attachments
public | customfields_pkey | index | rt_gsits_user |
customfields
public | customfieldvalues_pkey | index | rt_gsits_user |
customfieldvalues
public | disgroumem | index | rt_gsits_user |
cachedgroupmembers
public | groupmembers1 | index | rt_gsits_user |
groupmembers
public | groupmembers_pkey | index | rt_gsits_user |
groupmembers
public | groups1 | index | rt_gsits_user | groups
public | groups2 | index | rt_gsits_user | groups
public | groups3 | index | rt_gsits_user | groups
public | groups_pkey | index | rt_gsits_user | groups
public | links1 | index | rt_gsits_user | links
public | links_pkey | index | rt_gsits_user | links
public | objectclasses_pkey | index | postgres |
objectclasses
public | objectcustomfields_pkey | index | rt_gsits_user |
objectcustomfields
public | objectcustomfieldvalues1 | index | rt_gsits_user |
objectcustomfieldvalues
public | objectcustomfieldvalues2 | index | rt_gsits_user |
objectcustomfieldvalues
public | objectscrips1 | index | postgres |
objectscrips
public | objectscrips_pkey | index | postgres |
objectscrips
public | objecttopics_pkey | index | postgres |
objecttopics
public | principals2 | index | rt_gsits_user | principals
public | principals_pkey | index | rt_gsits_user | principals
public | queues1 | index | rt_gsits_user | queues
public | queues_pkey | index | rt_gsits_user | queues
public | scripactions_pkey | index | rt_gsits_user |
scripactions
public | scripconditions_pkey | index | rt_gsits_user |
scripconditions
public | scrips_pkey | index | rt_gsits_user | scrips
public | sessions_pkey | index | rt_gsits_user | sessions
public | templates_pkey | index | rt_gsits_user | templates
public | ticketcustomfieldvalues_pkey | index | rt_gsits_user |
objectcustomfieldvalues
public | tickets1 | index | rt_gsits_user | tickets
public | tickets2 | index | rt_gsits_user | tickets
public | tickets3 | index | rt_gsits_user | tickets
public | tickets6 | index | rt_gsits_user | tickets
public | tickets7 | index | rt_gsits_user | tickets
public | tickets8 | index | rt_gsits_user | tickets
public | tickets_pkey | index | rt_gsits_user | tickets
public | topics_pkey | index | postgres | topics
public | transactions1 | index | rt_gsits_user |
transactions
public | transactions2 | index | rt_gsits_user |
transactions
public | transactions_pkey | index | rt_gsits_user |
transactions
public | users1 | index | rt_gsits_user | users
public | users4 | index | rt_gsits_user | users
public | users_pkey | index | rt_gsits_user | users
(57 rows)
Both databases are running in the same postgres instance, so that should
clarify that there isn't a difference in the actual service's
configuration that would be causing problems.
--
Brian McNally
System Administrator, Genome Sciences
(206) 543-7363
On 09/26/2014 01:37 PM, Alex Vandiver wrote:
> On 09/26/2014 04:19 PM, Brian McNally wrote:
>> Note that the statement works fine for me on one RT 4.2.7 instance and
>> fails on the other. The difference in configuration is the original
>> database I was migrating from (a 3.6.6 RT instance). The DB upgrade
>> process for both was similar without any critical errors.
>
> As the SQL statement runs successfully in one place, and not in the
> other, I'm asking you to compare:
>
> 1. Database schemas for the two instances
> 2. Postgres configurations for the two instances
>
> Note that this appears to be a failure of "GROUP BY id" to properly note
> that id is a unique index, and as such is sufficient -- rather than
> having to list all columns. The logic to support this was originally
> introduced in PostgreSQL 9.1, and DBIx::SearchBuilder 1.66 now takes
> advantage of it if possible. If that "GROUP BY" doesn't work for you,
> it implies that:
> 1. You Scrips table somehow doesn't have "id" as a unique index
> 2. You're not actually running PostgreSQL 9.1 or higher (check client
> vs server libraries?)
> 3. Your PostgreSQL has somehow disabled this optimization
>
> That is what I mean by "check your postgres configuration."
> - Alex
>
More information about the rt-devel
mailing list