Sequences

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