Re: SELECT returning too many rows (?) - Mailing list pgsql-bugs

From rob
Subject Re: SELECT returning too many rows (?)
Date
Msg-id 1107874747.1509.180.camel@localhost.localdomain
Whole thread Raw
In response to Re: SELECT returning too many rows (?)  (Stephan Szabo <sszabo@megazone.bigpanda.com>)
Responses Re: SELECT returning too many rows (?)
List pgsql-bugs
> Hmm, could you run the above including the system-columns ctid, xmin,
> cmin, xmax, cmax?

processing=3D# select oid,  ctid, xmin, cmin, xmax, cmax, id, aid, status
from q_certs where oid =3D 15282219 ;
   oid    | ctid  |   xmin    |   cmin    |   xmax    |   cmax    | id
|  aid  | status=20
----------+-------+-----------+-----------+-----------+-----------+-----
+-------+--------
 15282219 | (3,5) | 174011432 |         1 |         1 | 174214469 | 224
| 23604 | 1
 15282219 | (5,5) | 174011432 |         1 |         1 | 174214469 | 224
| 23604 | 1
 15282219 | (7,5) | 174011432 | 174700216 | 174700216 |         1 | 224
| 23604 | 1
(3 rows)

Since it wraps, I'll just select those columns :

processing=3D# select oid,  ctid, xmin, cmin, xmax, cmax from q_certs
where oid =3D 15282219 ;
   oid    | ctid  |   xmin    |   cmin    |   xmax    |   cmax=20=20=20=20
----------+-------+-----------+-----------+-----------+-----------
 15282219 | (3,5) | 174011432 |         1 |         1 | 174214469
 15282219 | (5,5) | 174011432 |         1 |         1 | 174214469
 15282219 | (7,5) | 174011432 | 174700216 | 174700216 |         1

Different results, that's interesting. I'm afraid I don't know what
those columns are for.


> Just for completeness sake, what is _q and what does the trigger do?

_q is the same table structure. I have used inheritance to create new
'queues' yet able to select across them all. I don't actually use this
in production since indexes don't work across inherited tables and it
turned out it was quicker to foreach through all my queues in perl/php
to utilise each tables native indexes and munge the data together
programmatically. That's all by-the-by.

The trigger logs insert and update into another table - my own home
brewed transaction and change log. The transaction table is foreign
keyed to 'id' and includes only one INSERT log, as expected. It was a
good job I didn't foreign key on 'oid' instead :)

Thanks for the info.

--=20
Rob Fielding
rob@dsvr.net

www.dsvr.co.uk                                             Development
Designer Servers                                    Business Serve Plc

    "I don't pretend to understand Brannigans Law. I merely enforce it"
        - Zapp Brannigan

pgsql-bugs by date:

Previous
From: Stephan Szabo
Date:
Subject: Re: SELECT returning too many rows (?)
Next
From: Tom Lane
Date:
Subject: Re: BUG #1466: #maintenace_work_mem = 16384