Thread: Sequences
I'm hoping someone can tell me I'm wrong, but I'm thinking not. Anyway, here it goes: Sequence information is stored in their individual 1-row tables, with an entry is pg_class of relkind 'S'. So you can't really get a list of sequences with last_value in a single query without a butt-ugly pile of UNIONs in a query manufactured after a query on pg_class (i.e. SELECT * FROM seq1 UNION SELECT * FROM seq2...). And the result would have no schema name with the sequence name, unless I make my UNION query that much butt-uglier (i.e. SELECT schema1 || '.' || sequence_name,last_value FROM seq1 UNION SELECT schema1 || '.' || sequence_name, last_value FROM seq2,...). Tell me I have an elegant solution there, and I'll give you flattery points but I'll still laugh at you. Anyone have any other tricks? -------------------- Andrew Rawnsley President The Ravensfield Digital Resource Group, Ltd. (740) 587-0114 www.ravensfield.com
Andrew Rawnsley <ronz@ravensfield.com> writes:
> Sequence information is stored in their individual 1-row tables, with
> an entry is pg_class of relkind 'S'. So you can't
> really get a list of sequences with last_value in a single query
Nope, you can't ... and I surely hope you weren't expecting that the
last_values would all be simultaneously valid ...
What I'd try for this is
select relname, get_last_value(relname) from pg_class where relkind = 'S';
where get_last_value() is a plpgsql function that does an EXECUTE.
But you must realize that there will be time skew between the
last_values.
What is the problem you really want to solve?
regards, tom lane
On Friday, September 19, 2003, at 12:21 AM, Tom Lane wrote: > Andrew Rawnsley <ronz@ravensfield.com> writes: >> Sequence information is stored in their individual 1-row tables, with >> an entry is pg_class of relkind 'S'. So you can't >> really get a list of sequences with last_value in a single query > > Nope, you can't ... and I surely hope you weren't expecting that the > last_values would all be simultaneously valid ... > No, I don't expect that. I'm shooting for 'best I can do'. > What I'd try for this is > > select relname, get_last_value(relname) from pg_class where relkind = > 'S'; > > where get_last_value() is a plpgsql function that does an EXECUTE. > But you must realize that there will be time skew between the > last_values. > > What is the problem you really want to solve? > Hacking some semblance of sequence support into eRserver. The possible skew when gathering last_values doesn't bother me too much - the replicated system is out of sync to start with, and the hope is that everything will stay pretty much caught up. Its certainly possible for me to be caught with my pants down and have a sequence lagging the column its supposed to represent at time of failure. But a) its not likely, and b) I don't have much choice anyway. Something is better than nothing. Pays your money you takes your chances. I'll poke around with the function idea. Certain better than doing it all from the client side... > regards, tom lane > > ---------------------------(end of > broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if > your > joining column's datatypes do not match > -------------------- Andrew Rawnsley President The Ravensfield Digital Resource Group, Ltd. (740) 587-0114 www.ravensfield.com -------------------- Andrew Rawnsley President The Ravensfield Digital Resource Group, Ltd. (740) 587-0114 www.ravensfield.com
Oh, its a sticky problem, to be sure. Have to get something working at
some point, though...
You're point about approximate solutions is well taken.
On Friday, September 19, 2003, at 01:11 AM, Tom Lane wrote:
> Andrew Rawnsley <ronz@ravensfield.com> writes:
>> On Friday, September 19, 2003, at 12:21 AM, Tom Lane wrote:
>>> What is the problem you really want to solve?
>
>> Hacking some semblance of sequence support into eRserver.
>
> Hmm. I don't see a lot of value in an approximate solution. Either
> the
> sequence is up to date at the slave, or it is not. What's the point of
> "almost up to date"? You'd still have to take some action along the
> lines of "select setval('seq', (select max(col) from tab))" during any
> failover. If you have to do that, it doesn't matter what the sequence
> value was.
>
> Perhaps sequence increments could be broadcast to slaves as-is, using
> some variant of the existing erserver protocol that understands that
> these things happen outside transaction control.
>
> regards, tom lane
>
--------------------
Andrew Rawnsley
President
The Ravensfield Digital Resource Group, Ltd.
(740) 587-0114
www.ravensfield.com