Re: [SQL] Programatically switching database
ow writes: > Is there a way to programatically switch conn1 to use db2 without doing > disconnect-from-db1-connect-to-db2? Something like what "\c" does but to > be used independently from psql? I need this to be able to reuse a pool > of connections to db1 for actions on db1, db2 ... dbn. Nothing prevents you from keeping the connection to db1 open when you open a connection to db2. By the way, psql's "\c" command does exactly disconnect-from-db1-connect-to-db2. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Getting last insert value
Hello, I have a table with many fields, and one of the fields is of type serial. I will do many inserts, and there will be many repeats of the other fields in the table, but, obviously, the serial field will be unique for all the rows. How can I get the value of serial field of the last row I inserted. I know I can select the maximum value from the serial field, but is there a better way, preferably something that takes constant time. Thanks, Yasir Malik ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Getting last insert value
Le Samedi 15 Novembre 2003 15:18, vous avez écrit : > Hello, > I have a table with many fields, and one of the fields is of type serial. > I will do many inserts, and there will be many repeats of the other fields > in the table, but, obviously, the serial field will be unique for all the > rows. How can I get the value of serial field of the last row I inserted. > I know I can select the maximum value from the serial field, but is there > a better way, preferably something that takes constant time. > Thanks, > Yasir Malik > Doing a "select currval() from my_table" after your insert should work. For more details, see http://www.postgresql.org/docs/7.3/interactive/functions-sequence.html -- Guillaume . ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Programatically switching database
--- Peter Eisentraut wrote: > Nothing prevents you from keeping the connection to db1 open when you open > a connection to db2. By the way, psql's "\c" command does exactly > disconnect-from-db1-connect-to-db2. That's the whole point: I'm trying to avoid maintaining *separate* connection pools for each db. In other words, instead of having, say, 5 connection pools to 5 dbs with total of 1000 connections, I could've used just one (1) pool with 200 connections, if there was a way to "switch db" (or to do a cross-db query). Any ideas? Thanks __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Programatically switching database
ow writes: > That's the whole point: I'm trying to avoid maintaining *separate* connection > pools for each db. In other words, instead of having, say, 5 connection pools > to 5 dbs with total of 1000 connections, I could've used just one (1) pool with > 200 connections, if there was a way to "switch db" (or to do a cross-db query). I'm afraid that what you want to do is not possible. Perhaps you want to organize your data into schemas, not databases. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Getting last insert value
Le Samedi 15 Novembre 2003 16:40, vous avez écrit :
> Doing a "select currval() from my_table" after your insert should work.
>
Actually, this is
select currval('my_sequence')
Sorry about this.
> For more details, see
> http://www.postgresql.org/docs/7.3/interactive/functions-sequence.html
--
Guillaume
.
---(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
Re: [SQL] Getting last insert value
Guillaume LELARGE <[EMAIL PROTECTED]> writes:
> Doing a "select currval() from my_table" after your insert should work.
That's "select currval('my_table_pkcol_seq')" actually.
The above would have called the currval() function for every record of the
table which isn't what you want and in any case currval takes an argument.
--
greg
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] Programatically switching database
--- Peter Eisentraut <[EMAIL PROTECTED]> wrote: > I'm afraid that what you want to do is not possible. Perhaps you want to > organize your data into schemas, not databases. There's too much data to put it in one db. If anything happens to it, I'll never be able to restore (or dump) it in time. BTW, mySql has cross-db queries. Thanks __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Programatically switching database
--- Peter Eisentraut <[EMAIL PROTECTED]> wrote: > I'm afraid that what you want to do is not possible. Perhaps you want to > organize your data into schemas, not databases. There's too much data to put it in one db. If anything happens to it, I'll never be able to restore (or dump) it in time. BTW, mySql has cross-db queries. Thanks __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Programatically switching database
ow writes: > There's too much data to put it in one db. There is never too much data to be put in one database. > If anything happens to it, I'll never be able to restore (or dump) it in > time. You could just dump individual schemas. > BTW, mySql has cross-db queries. PostgreSQL has schemas, that's the same thing. PostgreSQL's "databases" are not the same thing as MySQL's "databases". -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Programatically switching database
--- Peter Eisentraut <[EMAIL PROTECTED]> wrote: > You could just dump individual schemas. How? The doc only mentions db: pg_dump [option...] [dbname] Then, how would I lock users out from the schema while it's being loaded? Thanks __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree ---(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
Re: [SQL] Getting last insert value
Thank you all for your help.
Yasir Malik
On Sat, 15 Nov 2003, Greg Stark wrote:
> Date: 15 Nov 2003 12:50:28 -0500
> From: Greg Stark <[EMAIL PROTECTED]>
> To: Guillaume LELARGE <[EMAIL PROTECTED]>
> Cc: Yasir Malik <[EMAIL PROTECTED]>, [EMAIL PROTECTED]
> Subject: Re: [SQL] Getting last insert value
>
>
> Guillaume LELARGE <[EMAIL PROTECTED]> writes:
>
> > Doing a "select currval() from my_table" after your insert should work.
>
> That's "select currval('my_table_pkcol_seq')" actually.
>
> The above would have called the currval() function for every record of the
> table which isn't what you want and in any case currval takes an argument.
>
> --
> greg
>
>
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings
>
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] Getting last insert value
what does that mean the 'last row'? if client 1 inserts then client 2, the last row is client 2. do you want to know what row client 1 inserted? Personally I think your question is too broad. What are you trying to accomplish? Ted --- Yasir Malik <[EMAIL PROTECTED]> wrote: > Hello, > I have a table with many fields, and one of the > fields is of type serial. > I will do many inserts, and there will be many > repeats of the other fields > in the table, but, obviously, the serial field will > be unique for all the > rows. How can I get the value of serial field of > the last row I inserted. > I know I can select the maximum value from the > serial field, but is there > a better way, preferably something that takes > constant time. > Thanks, > Yasir Malik > > ---(end of > broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Programatically switching database
--- ow <[EMAIL PROTECTED]> wrote: > How? The doc only mentions db: pg_dump [option...] [dbname] > > Then, how would I lock users out from the schema while it's being loaded? Never mind how, I see there's "-n namespace" option in 7.4. But still, how would I lock users out from the schema while it's being loaded? Thanks __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Programatically switching database
ow wrote: --- ow <[EMAIL PROTECTED]> wrote: How? The doc only mentions db: pg_dump [option...] [dbname] Then, how would I lock users out from the schema while it's being loaded? Never mind how, I see there's "-n namespace" option in 7.4. But still, how would I lock users out from the schema while it's being loaded? #!/bin/sh ( echo "start transaction;" cat $2 echo "commit transaction;" ) psql $1 then call it as reload_in_transaction my_db my_namespace.dump Since the whole dump will be restored inside of one transaction, nobody will see it while it's reloading. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Programatically switching database
Jan Wieck wrote: ow wrote: --- ow <[EMAIL PROTECTED]> wrote: How? The doc only mentions db: pg_dump [option...] [dbname] Then, how would I lock users out from the schema while it's being loaded? Never mind how, I see there's "-n namespace" option in 7.4. But still, how would I lock users out from the schema while it's being loaded? #!/bin/sh ( echo "start transaction;" cat $2 echo "commit transaction;" ) psql $1 gosh, there's the pipe missing before the psql then call it as reload_in_transaction my_db my_namespace.dump Since the whole dump will be restored inside of one transaction, nobody will see it while it's reloading. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Programatically switching database
--- Jan Wieck <[EMAIL PROTECTED]> wrote: > #!/bin/sh > > ( > echo "start transaction;" > cat $2 > echo "commit transaction;" > ) psql $1 > > > > then call it as > > reload_in_transaction my_db my_namespace.dump > > Since the whole dump will be restored inside of one transaction, nobody > will see it while it's reloading. Interesting idea. I know some RDBMSs that are very picky about DDL in transactions but it appears pgSql handles it without any problems. My concern though ... wouldn't pgSql server collapse when faced with transaction spawning across 100M+ records? And how would that affect overall restore time? I've should've tried it myself but I can't, my "regular" pg_restore has been running for 4+ hours, can't kill now ... Thanks __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
