[GENERAL] XML output & multiple SELECT queries

[Peter Billen]

Dear PostgreSQL users,

I would like to ask a question about outputting data as XML. Say I have two
tables:

team(integer id, text name);
player_of_team(integer id, integer team_id, text name); (team_id is FK to
team.id)

I would like to query both tables to get following example XML output:


 
 Garcia
 Robinho
 


Is this possible in one query? I'm thinking about something like this, but I
haven't figured it out so far:

SELECT XMLElement(name team, XMLAttributes(name as name), SELECT XMLElement(name
players, XMLForest(name)) FROM player_of_team WHERE team_id = t.id) FROM team t
WHERE t.name = 'Real Madrid';

In other words, is it possible to nest multiple SELECT queries in the XML output
functions?

Thanks in advance. Kind regards,

Peter


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] SERIAL datatype

[Peter Billen]

Hi all,

I would like to ask a question about the serial datatype. Say I have a 
field of type serial, and say for the sake of example that the range of 
a serial goes from 1 to 5 (inclusive). I insert 10 entries into the 
table, so the table is 'full':


INSERT INTO my_table (my_serial) VALUES (DEFAULT);
INSERT INTO my_table (my_serial) VALUES (DEFAULT);
INSERT INTO my_table (my_serial) VALUES (DEFAULT);
INSERT INTO my_table (my_serial) VALUES (DEFAULT);

Next I delete a random entry, say the one with value 3:

DELETE FROM my_table WHERE my_serial = 3;

Is it possible to insert a new entry? Will the serial sequence somehow 
be able to find the gap (3)?


The reason why I am asking is because I have a table in which constantly 
entries are being deleted and inserted. What happens if the serial 
sequence is exhausted? If it is not able to go the the next gap, how is 
it possible to keep inserting and deleting entries once the serial 
sequence has been exhausted? I can't find this anywhere in docs.


To me, it is perfectly possible that there is only one entry in the 
table, with a serial value equal to its upper limit.


Thanks in advance. Kind regards,

Peter

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] SERIAL datatype

[Peter Billen]

Oops, my example was a bit incorrectly edited.

I wanted to say that the range of a serial datatype goes from 1 to 5 
(incluse) and I insert five entries (not 10).


Peter

Peter Billen schreef:

Hi all,

I would like to ask a question about the serial datatype. Say I have a 
field of type serial, and say for the sake of example that the range 
of a serial goes from 1 to 5 (inclusive). I insert 10 entries into the 
table, so the table is 'full':


INSERT INTO my_table (my_serial) VALUES (DEFAULT);
INSERT INTO my_table (my_serial) VALUES (DEFAULT);
INSERT INTO my_table (my_serial) VALUES (DEFAULT);
INSERT INTO my_table (my_serial) VALUES (DEFAULT);

Next I delete a random entry, say the one with value 3:

DELETE FROM my_table WHERE my_serial = 3;

Is it possible to insert a new entry? Will the serial sequence somehow 
be able to find the gap (3)?


The reason why I am asking is because I have a table in which 
constantly entries are being deleted and inserted. What happens if the 
serial sequence is exhausted? If it is not able to go the the next 
gap, how is it possible to keep inserting and deleting entries once 
the serial sequence has been exhausted? I can't find this anywhere in 
docs.


To me, it is perfectly possible that there is only one entry in the 
table, with a serial value equal to its upper limit.


Thanks in advance. Kind regards,

Peter




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] SERIAL datatype

[Peter Billen]

My concern is not that the table will become full, but that the sequence 
will be exhausted. Doing INSERT, DELETE, INSERT, DELETE ... will exhaust 
the sequence. What will happen then? Do I have to manually re-order my 
serial values and reset the start sequence ID to MAX() + 1?


Thanks in advance,

Peter

ries van Twisk schreef:


On Aug 21, 2008, at 2:23 PM, Peter Billen wrote:


Oops, my example was a bit incorrectly edited.

I wanted to say that the range of a serial datatype goes from 1 to 5 
(incluse) and I insert five entries (not 10).


Peter

Peter Billen schreef:

Hi all,

I would like to ask a question about the serial datatype. Say I have 
a field of type serial, and say for the sake of example that the 
range of a serial goes from 1 to 5 (inclusive). I insert 10 entries 
into the table, so the table is 'full':


INSERT INTO my_table (my_serial) VALUES (DEFAULT);
INSERT INTO my_table (my_serial) VALUES (DEFAULT);
INSERT INTO my_table (my_serial) VALUES (DEFAULT);
INSERT INTO my_table (my_serial) VALUES (DEFAULT);

Next I delete a random entry, say the one with value 3:

DELETE FROM my_table WHERE my_serial = 3;

Is it possible to insert a new entry? Will the serial sequence 
somehow be able to find the gap (3)?


The reason why I am asking is because I have a table in which 
constantly entries are being deleted and inserted. What happens if 
the serial sequence is exhausted? If it is not able to go the the 
next gap, how is it possible to keep inserting and deleting entries 
once the serial sequence has been exhausted? I can't find this 
anywhere in docs.


To me, it is perfectly possible that there is only one entry in the 
table, with a serial value equal to its upper limit.


Thanks in advance. Kind regards,

Peter



May be you want to use BIGSERIAL if you are worried?

Ries





A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing in e-mail?





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] SERIAL datatype

[Peter Billen]

Thanks. I thought it was a bit counter-intuitive to have a BIGSERIAL 
while I will only have a few thousands of entries, which are updated (by 
DELETE and INSERT) constantly.


Thanks Scott,

Peter

Scott Marlowe schreef:

On Thu, Aug 21, 2008 at 1:08 PM, Peter Billen <[EMAIL PROTECTED]> wrote:
  

Hi all,

I would like to ask a question about the serial datatype. Say I have a field
of type serial, and say for the sake of example that the range of a serial
goes from 1 to 5 (inclusive). I insert 5 (ed) entries into the table, so the
table is 'full':

INSERT INTO my_table (my_serial) VALUES (DEFAULT);
INSERT INTO my_table (my_serial) VALUES (DEFAULT);
INSERT INTO my_table (my_serial) VALUES (DEFAULT);
INSERT INTO my_table (my_serial) VALUES (DEFAULT);

Next I delete a random entry, say the one with value 3:

DELETE FROM my_table WHERE my_serial = 3;

Is it possible to insert a new entry? Will the serial sequence somehow be
able to find the gap (3)?



No, sequences do not fill in gaps.

  

The reason why I am asking is because I have a table in which constantly
entries are being deleted and inserted. What happens if the serial sequence
is exhausted? If it is not able to go the the next gap, how is it possible
to keep inserting and deleting entries once the serial sequence has been
exhausted? I can't find this anywhere in docs.



Regular SERIAL type is limited to a 32 bit int.  BIGSERIAL uses a 64
bit int.  That will give you an upper limit of 2^63, assuming positive
values only in the sequence.  If you run out of that many values
you're running a seriously busy database over a very long time.

My rough guesstimate is that at 2000 inserts per second, it would take
approximately 145,865,043 years to exhaust a BIGSERIAL.  I might be
off by a factor of ten or so there.  But I don't think I am.  Note
that an insert rate of 2000 per second would exhaust a regular SERIAL
type (2^31 size) in 12 days.

  

To me, it is perfectly possible that there is only one entry in the table,
with a serial value equal to its upper limit.



That's fine too.  If you need gapless sequences, be prepared to pay
more in terms of overhead costs.  If you don't need gapless sequences
(and usually you don't) then use either SERIAL or BIGSERIAL.

  



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] problem with foreign keys + data-only backup

[Peter Billen]

Hi all,

I have a problem with foreign keys and data-only (no schema) backup. I 
have a simple table node (pseudo-SQL):


node
(
   integer node_idNOT NULLPRIMARY KEY;
   integer parent_node_id NULL;
)

It contains the following two entries:

node(1, NULL)  the rood
node(2, 1) a child of the root

When I do a data-only backup, the backup file contains following two lines:

INSERT INTO NODE (node_id, parent_node_id) VALUES (2, 1);
INSERT INTO NODE (node_id, parent_node_id) VALUES (1, NULL);

Restoring the backup file into another database ofcourse fails, because 
the parent_node_id (1) in the first INSERT statement refers to an 
unknown (to be added) node (in the second statement).


How do I make sure my backup orders the insert statements in a logical 
order?


This is how I make the backup:

pg_dump.exe -h ... -p 5432 -U ... --column-inserts --ignore-version 
--file=dump --format=t --data-only --verbose db


This is how I import the backup:

pg_restore.exe -h  -p 5432 -U ... --dbname db --format=t --verbose 
--table=channel dump


Thanks all. Kind regards,

Peter

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] disable/enable trigger and transaction

[Peter Billen]

Hi all,

Is the instruction 'ALTER TABLE x ENABLE/DISABLE TRIGGER y' part of the 
current transaction? That is, say that there are two ongoing 
transactions, T1 and T2. Is the trigger y still enabled in transaction 
T2 if it's disabled in transaction T1?


Thanks in advance. Kind regards,

Peter

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_dump restore as transaction?

[Peter Billen]

Quoting Owen Hartnett <[EMAIL PROTECTED]>:



If my perusal of the sql generated by pg_dump is correct, then it 
doesn't appear that it's wrapped in a transaction, and thus might be 
able to only complete a partial restore?


Or does

psql myDatabase 

Try to use pg_restore with the following option:

 -1, --single-transaction
  restore as a single transaction

Or psql with the following option:

 -1 ("one")  execute command file as a single transaction

Kind regards,

Peter



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] disallow updates on column or whole table

[Peter Billen]

Hi all,

What is the best way to disallow updates on a column of a table, or even 
on a whole table itself?


I can write a BEFORE UPDATE trigger which compares old.column_name(s) to 
new.column_name(s) and raise an exception if these values are different.


Are there better ways?

Certain properties cannot be changed once inserted. I would like to 
enforce that in my database schema.


Thanks in advance. Kind regards,

Peter

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general