Обсуждение: 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