Re: SELECT Question

Hi,
I have a column with a sequence in a table and want to allocate the
first 200 for special purpose thus starting the sequence from 200. For
the first 200 I have entries that will be added and removed.

Is there an easy way to write a select statement that returns me the
frist free number or any within the range of 200?
For example if 1-30, and 32-50 are occupied then i would like to fill in
the new entry with id 31.
I currently do it with a function but I was just wondering if there is a
way without it..

Thanks
Alex

> Is there an easy way to write a select statement that returns me the
> frist free number or any within the range of 200?
> For example if 1-30, and 32-50 are occupied then i would like to fill in
> the new entry with id 31.
> I currently do it with a function but I was just wondering if there is a
> way without it..
If I understand well, what something about

SELECT min(xxx) FROM table WHERE xxx<50

Best regards,
Lada 'Ray' Lostak
Unreal64 Develop group
http://www.orcave.com
http://www.unreal64.net

--------------------------------------------------------------------------
In the 1960s you needed the power of two C64s to get a rocket
to the moon. Now you need a machine which is a vast number
of times more powerful just to run the most popular GUI.

Thanks,
but that only gives me smallest number of the ones in use but not the
first free number.
Alex

Lada 'Ray' Lostak wrote:

>>Is there an easy way to write a select statement that returns me the
>>frist free number or any within the range of 200?
>>For example if 1-30, and 32-50 are occupied then i would like to fill in
>>the new entry with id 31.
>>I currently do it with a function but I was just wondering if there is a
>>way without it..
>>
>>
>If I understand well, what something about
>
> SELECT min(xxx) FROM table WHERE xxx<50
>
>Best regards,
>Lada 'Ray' Lostak
>Unreal64 Develop group
>http://www.orcave.com
>http://www.unreal64.net
>
>
>--------------------------------------------------------------------------
>In the 1960s you needed the power of two C64s to get a rocket
>to the moon. Now you need a machine which is a vast number
>of times more powerful just to run the most popular GUI.
>
>
>
>
>
>

> > Is there an easy way to write a select statement that returns me the
> > frist free number or any within the range of 200?
> > For example if 1-30, and 32-50 are occupied then i would like to fill in
> > the new entry with id 31.
> > I currently do it with a function but I was just wondering if there is a
> > way without it..
> If I understand well, what something about
>
> SELECT min(xxx) FROM table WHERE xxx<50
>
After I sent it, I saw I understand bad... Just woke up... Sorry :)

I personally think, you need small procedure do to that, because you want to
perform condition 'min(xxx)' on "unexisting" columns.

R.

yes i am doing it that way now, but though there may be another way ...
more out of curiosity

thanks anyway

Lada 'Ray' Lostak wrote:

>>>Is there an easy way to write a select statement that returns me the
>>>frist free number or any within the range of 200?
>>>For example if 1-30, and 32-50 are occupied then i would like to fill in
>>>the new entry with id 31.
>>>I currently do it with a function but I was just wondering if there is a
>>>way without it..
>>>
>>>
>>If I understand well, what something about
>>
>> SELECT min(xxx) FROM table WHERE xxx<50
>>
>>
>>
>After I sent it, I saw I understand bad... Just woke up... Sorry :)
>
>I personally think, you need small procedure do to that, because you want to
>perform condition 'min(xxx)' on "unexisting" columns.
>
>R.
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
>
>
>
>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Am Thursday 20 November 2003 08:32 schrieben Sie:
> > Is there an easy way to write a select statement that returns me the
> > frist free number or any within the range of 200?

As long as the set of numbers is not too big, the following might work:

CREATE TABLE legal_numbers ( num int );
INSERT INTO into legal_numbers VALUES ( 1 );
... do this with values from 1 to 200;

You can create this table once and for all.
Now the select is rather simple:

SELECT
min(num)
FROM
legal_numbers
WHERE
num not in ( SELECT id FROM other_table )
;

Mit freundlichem Gruß / With kind regards
Holger Klawitter
- --
info(at)klawitter(dot)de
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQE/vIha1Xdt0HKSwgYRAtGbAJ4xR+Naws0vHugt40GH+BJYY/3bAwCdG7t6
2ijqnA8Fm5Z8h4Zhw5H7p3s=
=xsBO
-----END PGP SIGNATURE-----

On Thu, 20 Nov 2003, Alex wrote:
> >>Is there an easy way to write a select statement that returns me the
> >>frist free number or any within the range of 200?
> >>For example if 1-30, and 32-50 are occupied then i would like to fill in
> >>the new entry with id 31.

If you had a table with an id column and 200 rows 1-200 you could do

SELECT MIN(idtab.id) FROM idtab LEFT JOIN realtab ON (idtab.id =
realtab.id AND realtab.id IS NULL)

A useful generic function would be one something like range(min,max) that
would return a set of rows so you wouldn't have to actually have a table.

Kris Jurka

On Thu, 20 Nov 2003 16:52:37 +0900, Alex <alex(at)meerkatsoft(dot)com> wrote:
>>>>Is there an easy way to write a select statement that returns me the
>>>>frist free number or any within the range of 200?
>>>>For example if 1-30, and 32-50 are occupied then i would like to fill in
>>>>the new entry with id 31.

Fortunately this is not the performance mailing list :-)

First free number:
SELECT max(t1.id) + 1
FROM t AS t1 INNER JOIN t AS t2
ON (t1.id < 200 AND t1.id < t2.id AND t2.id <= 200)
GROUP BY t2.id
HAVING max(t1.id) + 1 < t2.id
ORDER BY t2.id
LIMIT 1;

Make sure that there is always a row with id=0 and a row with id=200.

Any free number:
SELECT id - 1
FROM t
WHERE 1 < id AND id <= 200
AND NOT EXISTS (SELECT * FROM t AS t2 WHERE t2.id = t.id - 1)
LIMIT 1;

Always having a row with id=200 helps avoid unwanted corner cases.

One more:
SELECT coalesce(max(id), 0) + 1
FROM t
WHERE id <= 200
AND id = (SELECT count(*) FROM t AS t2 WHERE t2.id <= t.id);

This should work without any dummy rows. And it will not work, if id
is not unique or there is any row with id < 1.

Servus
Manfred

Kris Jurka wrote:
> A useful generic function would be one something like range(min,max) that
> would return a set of rows so you wouldn't have to actually have a table.
>

You mean like this?

CREATE OR REPLACE FUNCTION test(int,int) RETURNS SETOF int AS '
BEGIN
FOR i IN $1..$2 LOOP
RETURN NEXT i;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql' STRICT IMMUTABLE;

regression=# select * from test(4, 8);
test
------
4
5
6
7
8
(5 rows)

HTH,

Joe

Joe Conway <mail(at)joeconway(dot)com> writes:
> Kris Jurka wrote:
>> A useful generic function would be one something like range(min,max) that
>> would return a set of rows so you wouldn't have to actually have a table.

> You mean like this?

> CREATE OR REPLACE FUNCTION test(int,int) RETURNS SETOF int AS '
> BEGIN
> FOR i IN $1..$2 LOOP
> RETURN NEXT i;
> END LOOP;
> RETURN;
> END;
> ' LANGUAGE 'plpgsql' STRICT IMMUTABLE;

I was thinking of proposing that we provide something just about like
that as a standard function (written in C, not in plpgsql, so that it
would be available whether or not you'd installed plpgsql). There are
some places in the information_schema that desperately need it ---
right now, the value of FUNC_MAX_ARGS is effectively hard-wired into
some of the information_schema views, which means they are broken if
one changes that #define. We could fix this if we had a function like
the above and exported FUNC_MAX_ARGS as a read-only GUC variable.

regards, tom lane

Tom Lane wrote:
> Joe Conway <mail(at)joeconway(dot)com> writes:
>>CREATE OR REPLACE FUNCTION test(int,int) RETURNS SETOF int AS '
>>BEGIN
>> FOR i IN $1..$2 LOOP
>> RETURN NEXT i;
>> END LOOP;
>> RETURN;
>>END;
>>' LANGUAGE 'plpgsql' STRICT IMMUTABLE;
>
> I was thinking of proposing that we provide something just about like
> that as a standard function (written in C, not in plpgsql, so that it
> would be available whether or not you'd installed plpgsql). There are
> some places in the information_schema that desperately need it ---
> right now, the value of FUNC_MAX_ARGS is effectively hard-wired into
> some of the information_schema views, which means they are broken if
> one changes that #define. We could fix this if we had a function like
> the above and exported FUNC_MAX_ARGS as a read-only GUC variable.

I've been really busy on other-than-postgres stuff lately, but I'm
planning to carve out time next week to start doing some 7.5
development. I'll take this one if you want.

Joe

All,
thanks for the many suggestions
Alex

Manfred Koizar wrote:

>On Thu, 20 Nov 2003 16:52:37 +0900, Alex <alex(at)meerkatsoft(dot)com> wrote:
>
>
>>>>>Is there an easy way to write a select statement that returns me the
>>>>>frist free number or any within the range of 200?
>>>>>For example if 1-30, and 32-50 are occupied then i would like to fill in
>>>>>the new entry with id 31.
>>>>>
>>>>>
>
>Fortunately this is not the performance mailing list :-)
>
>First free number:
>SELECT max(t1.id) + 1
> FROM t AS t1 INNER JOIN t AS t2
> ON (t1.id < 200 AND t1.id < t2.id AND t2.id <= 200)
> GROUP BY t2.id
>HAVING max(t1.id) + 1 < t2.id
> ORDER BY t2.id
> LIMIT 1;
>
>Make sure that there is always a row with id=0 and a row with id=200.
>
>Any free number:
>SELECT id - 1
> FROM t
> WHERE 1 < id AND id <= 200
> AND NOT EXISTS (SELECT * FROM t AS t2 WHERE t2.id = t.id - 1)
> LIMIT 1;
>
>Always having a row with id=200 helps avoid unwanted corner cases.
>
>One more:
>SELECT coalesce(max(id), 0) + 1
> FROM t
> WHERE id <= 200
> AND id = (SELECT count(*) FROM t AS t2 WHERE t2.id <= t.id);
>
>This should work without any dummy rows. And it will not work, if id
>is not unique or there is any row with id < 1.
>
>Servus
> Manfred
>
>
>
>

Tom Lane wrote:
> I was thinking of proposing that we provide something just about like
> that as a standard function (written in C, not in plpgsql, so that it
> would be available whether or not you'd installed plpgsql). There are
> some places in the information_schema that desperately need it ---
> right now, the value of FUNC_MAX_ARGS is effectively hard-wired into
> some of the information_schema views, which means they are broken if
> one changes that #define. We could fix this if we had a function like
> the above and exported FUNC_MAX_ARGS as a read-only GUC variable.
>

First installment. The attached exports FUNC_MAX_ARGS as a read-only GUC
variable -- func_max_args. Comments?

While I was in guc.c, I also added short_desc to the definition of the
pg_settings view. I wasn't sure if I ought to add the long_desc too, and
if so, should it be it's own column in the view, or be concatenated with
short_desc -- any thoughts on that?

Current output looks like this:

regression=# \x
Expanded display is on.
regression=# select * from pg_settings where name = 'func_max_args';
-[ RECORD 1 ]-----------------------------------------------------------
name | func_max_args
setting | 32
short_desc | Shows the compiled-in maximum number of function arguments.
context | internal
vartype | integer
source | default
min_val | 32
max_val | 32

This will require a catalog version bump when I apply it (not done in
the attached patch).

Joe

Joe Conway <mail(at)joeconway(dot)com> writes:
> First installment. The attached exports FUNC_MAX_ARGS as a read-only GUC
> variable -- func_max_args. Comments?

One could make a good case that INDEX_MAX_KEYS should be exported along
with FUNC_MAX_ARGS, rather than letting people write client code that
assumes they are the same.

I was intending to propose that we also export the following as
read-only variables:
* NAMEDATALEN
* BLCKSZ
* integer-vs-float datetime flag
(Not sure about suitable GUC names for these --- func_max_args isn't out
of line as a GUC name, but surely BLCKSZ is.) NAMEDATALEN is needed for
many of the same reasons as FUNC_MAX_ARGS. BLCKSZ is probably useful
for pg_autovacuum. The datetime representation flag will be important
when people start using binary data transmission seriously --- without
it you can't tell what you'll get for a timestamp value. Essentially,
these are things we currently tell people to use pg_controldata to find
out, but that's quite an inconvenient solution.

> While I was in guc.c, I also added short_desc to the definition of the
> pg_settings view. I wasn't sure if I ought to add the long_desc too, and
> if so, should it be it's own column in the view, or be concatenated with
> short_desc -- any thoughts on that?

If it's there it should be separate. I think also there was some
feeling it should be called "extra_desc" not "long_desc".

> + /* Can't be set in postgresql.conf */
> + {"func_max_args", PGC_INTERNAL, UNGROUPED,
> + gettext_noop("Shows the compiled-in maximum number of function "
> + "arguments."),
> + NULL
> + },
> + &func_max_args,
> + FUNC_MAX_ARGS, FUNC_MAX_ARGS, FUNC_MAX_ARGS, NULL, NULL
> + },

Please set the GUC_NOT_IN_SAMPLE and GUC_DISALLOW_IN_FILE flag bits on
each of these variables, too. I know we are not using these flags for
anything yet, but we should try to get them right...

regards, tom lane

Tom Lane writes:

> One could make a good case that INDEX_MAX_KEYS should be exported along
> with FUNC_MAX_ARGS, rather than letting people write client code that
> assumes they are the same.

You can determine these values by looking into the system catalogs.

> I was intending to propose that we also export the following as
> read-only variables:
> * NAMEDATALEN

And this as well.

> * BLCKSZ

Why would anyone be interested in that?

> * integer-vs-float datetime flag

Here we should really decide on one representation in the near term.

--
Peter Eisentraut peter_e(at)gmx(dot)net

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Tom Lane writes:
>> One could make a good case that INDEX_MAX_KEYS should be exported along
>> with FUNC_MAX_ARGS, rather than letting people write client code that
>> assumes they are the same.

> You can determine these values by looking into the system catalogs.

You can, but that does not mean that you should. I saw how you'd made
the information_schema code induce the value of NAMEDATALEN from type
NAME's typlen, and frankly I think it's remarkably ugly and fragile.
I do *not* want to recommend that client code do similar things to
induce these values. If we do that then we'll be wiring extremely
low-level assumptions into client code forevermore. Which of these
do you want to support into the indefinite future?

current_setting("func_max_args")

(SELECT typlen/4 from pg_type where typname = 'oidvector' and
typnamespace = (select oid from pg_namespace
where nspname = 'pg_catalog'))

I realize you think that using GUC variables for this purpose is a bit
of a bastardization of the GUC concept, and I can't really argue that
it isn't. But the fact is that GUC has succeeded beyond your wildest
dreams, and you should not be surprised that people now want to
piggyback on all that nice mechanism for other purposes. If we were to
invent some other concept for "access to read-only config variables",
then we'd just have to duplicate some large fraction of the
infrastructure that already exists for GUC. Why bother?

>> * BLCKSZ

> Why would anyone be interested in that?

There was just a discussion a few days ago about the page size for large
objects, for which the correct answer was "BLCKSZ/4" IIRC. Whether
people actually *should* care about the page size of large objects I
dunno, but the fact is some of them *do* care.

>> * integer-vs-float datetime flag

> Here we should really decide on one representation in the near term.

[shrug] If push comes to shove on a single representation, we will rip
out all that int8 stuff and go back to float8. This isn't negotiable;
we can't have a system that doesn't have working datetime functionality
on a machine without int8. I don't see that happening, though, so I
think we are going to be stuck with a compile-time choice for a long
time to come.

regards, tom lane

Peter Eisentraut wrote:
> Tom Lane writes:
>
> > One could make a good case that INDEX_MAX_KEYS should be exported along
> > with FUNC_MAX_ARGS, rather than letting people write client code that
> > assumes they are the same.
>
> You can determine these values by looking into the system catalogs.

How, count? Seems we should give an easy API.

> > I was intending to propose that we also export the following as
> > read-only variables:
> > * NAMEDATALEN
>
> And this as well.

Again, why not make it easy.

> > * BLCKSZ
>
> Why would anyone be interested in that?

Performance/admin tools might need this --- you need it to get the disk
size based on the number of pages recorded in pg_class.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Tom Lane wrote:
> One could make a good case that INDEX_MAX_KEYS should be exported along
> with FUNC_MAX_ARGS, rather than letting people write client code that
> assumes they are the same.
>
> I was intending to propose that we also export the following as
> read-only variables:
> * NAMEDATALEN
> * BLCKSZ
> * integer-vs-float datetime flag

OK, the attached includes the above -- result looks like:

regression=# select * from pg_settings where category like 'Compile%';
-[ RECORD 1 ]----------------------------------------------
name | block_size
setting | 8192
category | Compiled-in Options
short_desc | Shows size of a disk block
extra_desc |
context | internal
vartype | integer
source | default
min_val | 8192
max_val | 8192
-[ RECORD 2 ]----------------------------------------------
name | func_max_args
setting | 32
category | Compiled-in Options
short_desc | Shows the maximum number of function arguments
extra_desc |
context | internal
vartype | integer
source | default
min_val | 32
max_val | 32
-[ RECORD 3 ]----------------------------------------------
name | index_max_keys
setting | 32
category | Compiled-in Options
short_desc | Shows the maximum number of index keys
extra_desc |
context | internal
vartype | integer
source | default
min_val | 32
max_val | 32
-[ RECORD 4 ]----------------------------------------------
name | integer_datetimes
setting | on
category | Compiled-in Options
short_desc | Datetimes are integer based
extra_desc |
context | internal
vartype | bool
source | default
min_val |
max_val |
-[ RECORD 5 ]----------------------------------------------
name | name_data_len
setting | 63
category | Compiled-in Options
short_desc | Shows the maximum identifier length
extra_desc |
context | internal
vartype | integer
source | default
min_val | 63
max_val | 63

> If it's there it should be separate. I think also there was some
> feeling it should be called "extra_desc" not "long_desc".

Done. Also added "category" which displays config_group_names[conf->group]

> Please set the GUC_NOT_IN_SAMPLE and GUC_DISALLOW_IN_FILE flag bits on
> each of these variables, too. I know we are not using these flags for
> anything yet, but we should try to get them right...

Done.

I'll update the docs once I'm sure we're done iterating on these changes.

Any further comments?

Joe

Joe Conway <mail(at)joeconway(dot)com> writes:
> name | name_data_len
> setting | 63
> short_desc | Shows the maximum identifier length

Defining the value as NAMEDATALEN-1 is reasonable (I was thinking of
suggesting that myself), but it seems like a recipe for confusion to
use name_data_len to refer to NAMEDATALEN-1. Perhaps the GUC variable
name should be max_name_len or some such.

Also, should func_max_args and index_max_keys become max_func_args and
max_index_keys?

I'm not all that concerned about the names personally, but I want to
forestall any temptation for Bruce to start renaming these values later,
as he's felt free to do in the past ;-). My expectation is that the
names of these GUC variables will get embedded into client-side code
fairly quickly, and so it will not do to fool around with the names
later. We must decide what the naming convention is and then stick to
it.

regards, tom lane

Tom Lane wrote:
> Perhaps the GUC variable name should be max_name_len or some such.
>
> Also, should func_max_args and index_max_keys become max_func_args and
> max_index_keys?

That sounds good to me:

-[ RECORD 3 ]----------------------------------------------
name | max_func_args
setting | 32
-[ RECORD 4 ]----------------------------------------------
name | max_index_keys
setting | 32
-[ RECORD 5 ]----------------------------------------------
name | max_name_len
setting | 63

I'll finish up the docs and commit this tomorrow, barring strong
complaints. It will require an initdb -- should I hold off for other
pending changes also requiring initdb?

Joe

Joe Conway <mail(at)joeconway(dot)com> writes:
> It will require an initdb -- should I hold off for other
> pending changes also requiring initdb?

No, there's no particular reason to avoid initdbs during development
cycles. That's why we have catversion in the first place ...

regards, tom lane

Joe Conway wrote:
> Tom Lane wrote:
> > One could make a good case that INDEX_MAX_KEYS should be exported along
> > with FUNC_MAX_ARGS, rather than letting people write client code that
> > assumes they are the same.
> >
> > I was intending to propose that we also export the following as
> > read-only variables:
> > * NAMEDATALEN
> > * BLCKSZ
> > * integer-vs-float datetime flag
>
> OK, the attached includes the above -- result looks like:
>
> regression=# select * from pg_settings where category like 'Compile%';
> -[ RECORD 1 ]----------------------------------------------
> name | block_size

OK. Should that be page_size? Not sure but block size sounds more like
a hardware setting. I know we call it BLCKSZ in our code but page size
seems more appropriate. Not sure.

> name | func_max_args
> name | index_max_keys

Should that be max_func_args and max_index_args? Seems more natural.
Should we spell out function? Probably. We already have
check_*function*_bodies.

> name | integer_datetimes
> short_desc | Datetimes are integer based

This one has me confused. "Datetimes are integer based" is a statement,
as is the variable name. Should it be "integer_datetime_storage" or
something else?

> name | name_data_len

Is "name" a good description, or is "identifier" better, identifier_length?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:
> Joe Conway wrote:
>>name | block_size
>
> OK. Should that be page_size? Not sure but block size sounds more like
> a hardware setting. I know we call it BLCKSZ in our code but page size
> seems more appropriate. Not sure.

Seems like block_size is more appropriate to me. Any other opinions out
there? In bufpage.h I see this description of a page:

/*
* A postgres disk page is an abstraction layered on top of a postgres
* disk block (which is simply a unit of i/o, see block.h).

I guess the ral question is whether the uses for this information really
care about block size or page size -- i.e. if they weren't the same,
which would be the one you want to know?

>>name | func_max_args
>>name | index_max_keys
> Should that be max_func_args and max_index_args? Seems more natural.
> Should we spell out function? Probably. We already have
> check_*function*_bodies.

Agreed. Now:
name | max_function_args
name | max_identifier_length
name | max_index_keys

>>name | integer_datetimes
>>short_desc | Datetimes are integer based
>
> This one has me confused. "Datetimes are integer based" is a statement,
> as is the variable name. Should it be "integer_datetime_storage" or
> something else?

Well the configure option is:
--enable-integer-datetimes
so "integer_datetimes" seemed natural to me.

The description is a statement because the option is boolean, i.e. the
statement "Datetimes are integer based" is either "true" or "false"
("on" or "off", etc). How stongly do you feel about it? I don't think
"integer_datetime_storage" is accurate in any case.

>>name | name_data_len
> Is "name" a good description, or is "identifier" better, identifier_length?

Agreed -- see above.

Joe

Tom Lane writes:

> There was just a discussion a few days ago about the page size for large
> objects, for which the correct answer was "BLCKSZ/4" IIRC. Whether
> people actually *should* care about the page size of large objects I
> dunno, but the fact is some of them *do* care.

Maybe we should provide specific functions to access this information, so
client applications don't have to hardcode these formulas.

--
Peter Eisentraut peter_e(at)gmx(dot)net

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Tom Lane writes:
>> There was just a discussion a few days ago about the page size for large
>> objects, for which the correct answer was "BLCKSZ/4" IIRC. Whether
>> people actually *should* care about the page size of large objects I
>> dunno, but the fact is some of them *do* care.

> Maybe we should provide specific functions to access this information, so
> client applications don't have to hardcode these formulas.

That's exactly what this thread is about: current_setting() is the
proposed access function ...

I'm not convinced that large object pagesize is interesting enough to
deserve its own GUC variable, but if someone wanted to make that case
I'm certainly open to listening.

regards, tom lane

Joe Conway wrote:
> >>name | func_max_args
> >>name | index_max_keys
> > Should that be max_func_args and max_index_args? Seems more natural.
> > Should we spell out function? Probably. We already have
> > check_*function*_bodies.
>
> Agreed. Now:
> name | max_function_args
> name | max_identifier_length
> name | max_index_keys

Nice.

> >>name | integer_datetimes
> >>short_desc | Datetimes are integer based
> >
> > This one has me confused. "Datetimes are integer based" is a statement,
> > as is the variable name. Should it be "integer_datetime_storage" or
> > something else?
>
> Well the configure option is:
> --enable-integer-datetimes
> so "integer_datetimes" seemed natural to me.
>
> The description is a statement because the option is boolean, i.e. the
> statement "Datetimes are integer based" is either "true" or "false"
> ("on" or "off", etc). How stongly do you feel about it? I don't think
> "integer_datetime_storage" is accurate in any case.

Not strongly. Keep it unchanged.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:
> Joe Conway wrote:
>>The description is a statement because the option is boolean, i.e. the
>>statement "Datetimes are integer based" is either "true" or "false"
>>("on" or "off", etc). How stongly do you feel about it? I don't think
>>"integer_datetime_storage" is accurate in any case.
>
> Not strongly. Keep it unchanged.
>

Any more thoughts on block_size (or page_size)?

Thanks,

Joe

Joe Conway wrote:
> Bruce Momjian wrote:
> > Joe Conway wrote:
> >>The description is a statement because the option is boolean, i.e. the
> >>statement "Datetimes are integer based" is either "true" or "false"
> >>("on" or "off", etc). How stongly do you feel about it? I don't think
> >>"integer_datetime_storage" is accurate in any case.
> >
> > Not strongly. Keep it unchanged.
> >
>
> Any more thoughts on block_size (or page_size)?

When I think of block size I think of disk blocks, and when I think of
pages I think of memory pages. Unfortunately, neither is a database
page.

I guess my point is that we have heap pages and index pages, but no one
calls them heap blocks or index blocks, and I am not sure I would know
what they meant if they said that.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:
>>Any more thoughts on block_size (or page_size)?
>
> When I think of block size I think of disk blocks, and when I think of
> pages I think of memory pages. Unfortunately, neither is a database
> page.
>
> I guess my point is that we have heap pages and index pages, but no one
> calls them heap blocks or index blocks, and I am not sure I would know
> what they meant if they said that.
>

OK, I'll go with "page_size".

Thanks,

Joe

Joe Conway writes:

> Any more thoughts on block_size (or page_size)?

It's always been some variant spelling of "block size", and I see no
reason to change the terminology.

--
Peter Eisentraut peter_e(at)gmx(dot)net

Peter Eisentraut wrote:
> Joe Conway writes:
>
> > Any more thoughts on block_size (or page_size)?
>
> It's always been some variant spelling of "block size", and I see no
> reason to change the terminology.

Yes, that is from a coder's perspective, but from the user/admin
perspective, it seems more like page, and in fact we probably would call
it page if we were starting from scratch.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:
> Peter Eisentraut wrote:
>>Joe Conway writes:
>>>Any more thoughts on block_size (or page_size)?
>>
>>It's always been some variant spelling of "block size", and I see no
>>reason to change the terminology.
>
> Yes, that is from a coder's perspective, but from the user/admin
> perspective, it seems more like page, and in fact we probably would call
> it page if we were starting from scratch.

Hmm, I don't feel strongly either way on this, but I guess I lean toward
block_size myself. Any other opinions out there?

block_size or page_size or something else?

Joe

Tom Lane wrote:
> I was thinking of proposing that we provide something just about like
> that as a standard function (written in C, not in plpgsql, so that it
> would be available whether or not you'd installed plpgsql). There are
> some places in the information_schema that desperately need it ---
> right now, the value of FUNC_MAX_ARGS is effectively hard-wired into
> some of the information_schema views, which means they are broken if
> one changes that #define. We could fix this if we had a function like
> the above and exported FUNC_MAX_ARGS as a read-only GUC variable.
>

The attached patch introduces a C function as discussed above. Looks
like this:

regression=# select * from pg_generate(42,45);
pg_generate
-------------
42
43
44
45
(4 rows)

It also makes use of the function to replace the hard-wired parts of the
information_schema.

I have not yet made documentation changes, pending an answer to this and
other questions: what should this function be called? I'm at a loss as
to a good name -- the idea of the name pg_generate() was that the
function acts as a non-persistent sequence generator, but I don't really
like that name.

Any ideas, or other comments? For example, should pg_generate() allow a
finish value < start and therefore count backward? Should there be a
three argument version allowing a step size?

Thanks,

Joe

Joe Conway wrote:
> Tom Lane wrote:
>> I was thinking of proposing that we provide something just about like
>> that as a standard function (written in C, not in plpgsql, so that it
>> would be available whether or not you'd installed plpgsql). There are
>> some places in the information_schema that desperately need it ---
>> right now, the value of FUNC_MAX_ARGS is effectively hard-wired into
>> some of the information_schema views, which means they are broken if
>> one changes that #define. We could fix this if we had a function like
>> the above and exported FUNC_MAX_ARGS as a read-only GUC variable.
>
> The attached patch introduces a C function as discussed above. Looks
> like this:

The attached incorporates the feedback received. Specifically there is
now an int8 version of the function, and I left it as a simple
start-to-finish sequence generator. Result looks like this:

regression=# select * from pg_generate_sequence(4, 8);
pg_generate_sequence
----------------------
4
5
6
7
8
(5 rows)

regression=# select * from pg_generate_sequence(8, 4);
ERROR: finish is less than start

regression=# select * from pg_generate_sequence(8000000000, 8000000004);
pg_generate_sequence
----------------------
8000000000
8000000001
8000000002
8000000003
8000000004
(5 rows)

regression=# select * from pg_generate_sequence(3,8000000000);
ERROR: range of start to finish is too large
HINT: start to finish range must be less than 4294967295

I'm still not sure the name is the best -- other ideas welcome. Also,
I'm not sure if it would be a good thing, or too confusing, to document
pg_generate_sequence() on the "Sequence Manipulation Functions" page in
the docs. Any opinions on that?

If there are no objections I'll commit in 24 hours or so. Barring better
ideas, I'll probably add pg_generate_sequence() to "Sequence
Manipulation Functions".

Thanks,

Joe

Joe Conway <mail(at)joeconway(dot)com> writes:
> regression=# select * from pg_generate_sequence(8, 4);
> ERROR: finish is less than start

Hm, would it be better just to return an empty set? Certainly I'd
expect pg_generate_sequence(1,0) to return an empty set with no error.

> regression=# select * from pg_generate_sequence(3,8000000000);
> ERROR: range of start to finish is too large
> HINT: start to finish range must be less than 4294967295

Is there a good reason for that restriction? (I've never thought it was
good design for the SRF API to assume that the number of iterations
could be determined in advance, anyway.)

> I'm not sure if it would be a good thing, or too confusing, to document
> pg_generate_sequence() on the "Sequence Manipulation Functions" page in
> the docs. Any opinions on that?

It is *not* a sequence function in the sense used on that page. I'm not
quite sure where to put it, but don't give people the impression that it
is related to sequence objects.

I was going to say "pg_generate_sequence" is a fine name, but after
thinking about the lack of relation to sequence objects I think we ought
not use "sequence" in the name. How about "pg_generate_series" or
"pg_generate_set"? Actually I think you could leave off the pg_ prefix
and just make it generate_series or generate_set. It's not
Postgres-specific in the same way that, say. pg_get_indexdef is.

Maybe the best documentation answer is to create a new subsection in the
Functions chapter. This may be our first standard set-returning
function but I bet it will not be the last, so the shortness of the
subsection doesn't bother me.

regards, tom lane

Tom Lane wrote:
> Joe Conway <mail(at)joeconway(dot)com> writes:
>>regression=# select * from pg_generate_sequence(8, 4);
>>ERROR: finish is less than start
>
> Hm, would it be better just to return an empty set? Certainly I'd
> expect pg_generate_sequence(1,0) to return an empty set with no error.

OK -- for this and other concerns below, I bit the bullet and decided to
support descending series and step sizes other than one. Now it does this:

regression=# select * from generate_series(8, 4);
generate_series
-----------------
8
7
6
5
4
(5 rows)

regression=# select * from generate_series(8, 4, 2);
generate_series
-----------------
8
6
4
(3 rows)

regression=# select * from generate_series(8000000000, 8000000004, 2);
generate_series
-----------------
8000000000
8000000002
8000000004
(3 rows)

regression=# select * from generate_series(8000000004, 8000000000, 3);
generate_series
-----------------
8000000004
8000000001
(2 rows)

regression=# select * from generate_series(8000000004, 8000000000, -3);
ERROR: step value must be greater than 0
HINT: Use start greater than finish to create a descending series.

>>regression=# select * from pg_generate_sequence(3,8000000000);
>>ERROR: range of start to finish is too large
>>HINT: start to finish range must be less than 4294967295
>
> Is there a good reason for that restriction? (I've never thought it was
> good design for the SRF API to assume that the number of iterations
> could be determined in advance, anyway.)

See above -- fixed. But I'm not going to try to return > 4 billion
values to illustrate ;-)

> Actually I think you could leave off the pg_ prefix
> and just make it generate_series or generate_set.

OK -- made it generate_series().

> Maybe the best documentation answer is to create a new subsection in the
> Functions chapter. This may be our first standard set-returning
> function but I bet it will not be the last, so the shortness of the
> subsection doesn't bother me.

Agreed. I'll start this post-superbowl :-)

I'll apply in 24-48 hours if there are no further comments.

Thanks,

Joe

p.s. I did a `make distclean` prior to creating the attached diff. Do
the lines at the top, e.g.:
? src/bin/pg_id/.deps
? src/bin/pg_id/pg_id
...
indicate stuff not being cleaned up when it ought to be?

Joe Conway <mail(at)joeconway(dot)com> writes:
> Tom Lane wrote:
>> Joe Conway <mail(at)joeconway(dot)com> writes:
>>> regression=# select * from pg_generate_sequence(8, 4);
>>> ERROR: finish is less than start
>>
>> Hm, would it be better just to return an empty set? Certainly I'd
>> expect pg_generate_sequence(1,0) to return an empty set with no error.

> OK -- for this and other concerns below, I bit the bullet and decided to
> support descending series and step sizes other than one. Now it does this:

> regression=# select * from generate_series(8, 4);
> generate_series
> -----------------
> 8
> 7
> 6
> 5
> 4
> (5 rows)

And how do I get a zero-size set out of it? I think it's a really bad
idea to silently assume descending is meant if start > finish --- that
will create boundary-case bugs in many scenarios. A looping construct
that cannot iterate zero times is broken and dangerous (see Fortran DO
loops for context ... folklore has it that Mariner II was lost to
exactly such a bug).

If you want to allow the 3-parameter form to specify a negative step
size, that's fine. But don't use a heuristic to guess the intended
step direction.

> p.s. I did a `make distclean` prior to creating the attached diff. Do
> the lines at the top, e.g.:
> ? src/bin/pg_id/.deps
> ? src/bin/pg_id/pg_id
> ...
> indicate stuff not being cleaned up when it ought to be?

Something odd there. The src/bin/pg_id subdirectory should be entirely
gone in recent checkouts, and I'm not sure why you're seeing .so files
still laying about but they shouldn't be there either ...

regards, tom lane

Tom Lane wrote:
> folklore has it that Mariner II was lost to exactly such a bug).

Ouch -- got the point.

> If you want to allow the 3-parameter form to specify a negative step
> size, that's fine. But don't use a heuristic to guess the intended
> step direction.

The attached patch implements the semantics you're looking for (I
think). Also attached is my test case output.

The one corner case not discussed is a step size of zero. Currently it
returns zero rows, but I considered having it generate an ERROR.

OK to commit?

Thanks,

Joe

Joe Conway <mail(at)joeconway(dot)com> writes:
> The one corner case not discussed is a step size of zero. Currently it
> returns zero rows, but I considered having it generate an ERROR.

I'd go for ERROR --- can't think of any reason to do otherwise, nor
any standard programming language that wouldn't consider that an error.

> OK to commit?

Don't forget to bump the catversion number.

regards, tom lane

Joe Conway <mail(at)joeconway(dot)com> writes:
> Tom Lane wrote:
>> folklore has it that Mariner II was lost to exactly such a bug).

> Ouch -- got the point.

BTW, I think I was beating you over the head with an urban legend.
Some idle googling revealed the true facts of the Mariner failure:
http://www.rchrd.com/Misc-Texts/Famous_Fortran_Errors

Nonetheless, it's well established that loops should be able to
do nothing when doing nothing is called for. Even Fortran got the
point after awhile...

regards, tom lane

Tom Lane wrote:
> BTW, I think I was beating you over the head with an urban legend.
> Some idle googling revealed the true facts of the Mariner failure:
> http://www.rchrd.com/Misc-Texts/Famous_Fortran_Errors

Oh well, I've been beat over the head with worse things, at least
metaphorically ;-). Interesting reading though.

Joe

Tom Lane wrote:
> Maybe the best documentation answer is to create a new subsection in the
> Functions chapter. This may be our first standard set-returning
> function but I bet it will not be the last, so the shortness of the
> subsection doesn't bother me.

A first shot at documentation for generate_series() is available here in
html form:
http://www.joeconway.com/functions-srf.html

Feedback welcome.

Thanks,

Joe

Joe Conway <mail(at)joeconway(dot)com> writes:
> A first shot at documentation for generate_series() is available here in
> html form:
> http://www.joeconway.com/functions-srf.html
> Feedback welcome.

This bit seems unnecessarily vague:

Depending on the requested combination of start, stop, and step, it is
possible to return zero rows.

I think you can provide a precise specification without losing
simplicity of explanation. Maybe something like

When step is positive, zero rows are returned if start > stop.
Conversely, when step is negative, zero rows are returned if
start < stop. It is an error for step to be zero.

... and then carry on with the examples, which seem fine (although
the one showing the error for step=0 might be thought redundant
with the text).

regards, tom lane

> I think you can provide a precise specification without losing
> simplicity of explanation. Maybe something like
>
> When step is positive, zero rows are returned if start > stop.
> Conversely, when step is negative, zero rows are returned if
> start < stop. It is an error for step to be zero.

Having something that generates a list of dates would be handy, however
I guess you can do it with the current series generator by adding that
many day intervals to a base date...

Chris

Clinging to sanity, chriskl(at)familyhealth(dot)com(dot)au (Christopher Kings-Lynne) mumbled into her beard:
>> I think you can provide a precise specification without losing
>> simplicity of explanation. Maybe something like
>> When step is positive, zero rows are returned if start > stop.
>> Conversely, when step is negative, zero rows are returned if
>> start < stop. It is an error for step to be zero.
>
> Having something that generates a list of dates would be handy,
> however I guess you can do it with the current series generator by
> adding that many day intervals to a base date...

Yes, that would be the way to do it.

Having generators (I'm not clear yet on whether it's more like Icon
generators or Common Lisp SERIES, or APL iota :-)) is a very useful
thing indeed.
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','ntlug.org').
http://www3.sympatico.ca/cbbrowne/wp.html
"One often contradicts an opinion when what is uncongenial is really
the tone in which it was conveyed." -- Nietzsche

Christopher Kings-Lynne wrote:
> Having something that generates a list of dates would be handy, however
> I guess you can do it with the current series generator by adding that
> many day intervals to a base date...

Seems to work:

regression=# select current_date + s.a as dates from
generate_series(1,3) as s(a);
dates
------------
2004-02-05
2004-02-06
2004-02-07
(3 rows)

Or even:

regression=# select current_date + s.a * '1 week'::interval as dates
from generate_series(1,3) as s(a);
dates
---------------------
2004-02-11 00:00:00
2004-02-18 00:00:00
2004-02-25 00:00:00
(3 rows)

Joe

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Joe Conway wrote:
| Tom Lane wrote:
|
|> Maybe the best documentation answer is to create a new subsection in the
|> Functions chapter. This may be our first standard set-returning
|> function but I bet it will not be the last, so the shortness of the
|> subsection doesn't bother me.
|
|
| A first shot at documentation for generate_series() is available here in
| html form:
| http://www.joeconway.com/functions-srf.html

I seen there:

select * from generate_series(5,1,-2);
~ generate_series
- -----------------
~ 5
~ 3
~ 1
(3 rows)

I understood on your past posts that instead this result
was obtained with:

select * from generate_series(5,1,2);
~ generate_series
- -----------------
~ 5
~ 3
~ 1
(3 rows)

I think that is better have:
~ if start < end the series is ascending
~ if start > end the series is descending

~ if step > 0 the series is ascending
~ if step < 0 the series is descending
~ ( step can not be 0 )

if ( start < end ) and ( step < 0 ) the result set is empty
if ( start > end ) and ( step > 0 ) the result set is empty

IMHO this is the more natural behavior.

my 2 cents.

Regards
Gaetano Mendola

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFAIYxo7UpzwH2SGd4RAnr9AJ4//4AO9VaIQmweneB0BJe8DvKJQgCfSJOF
ejQN8TlGBjvntxoBJgk3uIk=
=HcPJ
-----END PGP SIGNATURE-----

Gaetano Mendola wrote:
> select * from generate_series(5,1,-2);

> I understood on your past posts that instead this result
> was obtained with:
>
> select * from generate_series(5,1,2);
> ~ generate_series
> - -----------------
> ~ 5
> ~ 3
> ~ 1
> (3 rows)

Tom objected to the original, so what you now see is what was agreed upon.

> ~ ( step can not be 0 )
>
> if ( start < end ) and ( step < 0 ) the result set is empty
> if ( start > end ) and ( step > 0 ) the result set is empty

Reread the thread. That was the conclusion and what the proposed
documentation is at least trying to convey. As Tom pointed out earlier,
I need to add a bit more detail to it.

Joe