Re: [GENERAL] RHEL
On Tuesday 11 November 2003 11:57 pm, Tom Lane wrote: > Adam Haberlach <[EMAIL PROTECTED]> writes: > > I was, a few minutes ago, stunned to discover that as far as I can > > tell, the postgres server is not part of Red Hat Server ES > Feel free to let Red Hat know that you're unhappy about this. > (Not totally unbiased here ... I'm getting *very* tired about RH's > internal indecision about their extent of commitment to Postgres. > I think frequent whacks-upside-the-head from paying customers may > be the only way to get upper management to sit up and take notice.) The RHEL3 beta (taroon) had rh-postgresql-server built and included. Does RHEL3 not include this package? -- Lamar Owen Director of Information Technology Pisgah Astronomical Research Institute 1 PARI Drive Rosman, NC 28772 (828)862-5554 www.pari.edu ---(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: [GENERAL] More Praise for 7.4RC2
On Wed, 2003-11-12 at 09:04, jake johnson wrote: I also posted about the performance increase of 7.4, but I think that much of the difference you're seeing (because it's such a large difference) is probably due to the cleanliness of a newly restored database from backup. I agree that this seems likely, except that the 7.3.4 database is vacuumed nightly, and analyzed periodically. And about a week ago I reclustered on the index intended to most facilitate this select. Furthermore, merely hardcoding the subselect result achieves a tremendous improvement (which was the workaround I used). So, I'm pretty sure that it's not a vacuum, index use, or cleanliness issue. I also meant to add in my original post that the system is a dual 2.4G xeon with 4GB of RAM. -Reece -- Reece Hart, http://www.in-machina.com/~reece/, GPG:0x25EC91A0 0xD178AAF9
Re: [GENERAL] plpgsql return setof integer?
On Wed, Nov 12, 2003 at 05:35:40PM -0500, Christopher Murtagh wrote:
> chris=# select htsearch('sample_return2.txt','dbname');
> htsearch
> --
> {HASH(0x835c298),2100,2113,2114}
> (1 row)
>
> Any obvious thing that I'm doing wrong? I'm using 7.4RC2. As always, any
> help or info would be much appreciated. Bonus points if someone knows
> what the HASH is. :-)
You are initializing the array incorrectly in your htdig(text, text)
function. {} is the hash reference you are seeing; HASH(0xwhatever) is
its text representation. Do
my @Result = ();
instead, or leave it uninitialized.
--
Alvaro Herrera ()
"Un poeta es un mundo encerrado en un hombre" (Victor Hugo)
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] pl/perl function life and variable scope - concurrency problem?
Christopher Murtagh <[EMAIL PROTECTED]> writes: > Thanks to a lot of help on this list, I've managed to get my pl/perl > function working. However, I have an unexpected result. Here's a simple > way to reproduce this problem: > > CREATE or REPLACE FUNCTION perltest(integer) > returns integer as ' > $MyInt = $MyInt + 1; > return $MyInt; > ' language plperlu; There's a reason Perl has "my" variables. Use them. ;) -Doug ---(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: [GENERAL] embedded postgresql
jini us wrote: Hi, I am starting a new project where I intend to use embedded database server in my win32 application. I intend to use VC++ microsoft studio 6.0 as my development environment. The postgres.org website seems to be catering for people with all sorts of requirements and platforms. 1. Where can I find the binary distribution so that I can use postgres server as an embedded database. I assume it is a .dll No... PostgreSQL is a database server, thus you connect to it via TCP/IP or domain sockets (preferrably TCP/IP). Also an emulation layer such as Cygwin will be required to use PostgreSQL on Windows. 2. Also I would like to have the documentation which shows how to start & stop the server programmatically and execute sql, to create a database, use select insert , update etc. www.postgresql.org click on docs. 3. Is it correct that I do not have to pay any user licences. Postgres is free not only for development but also I can include it in my software package. Yes that is correct. Thanks. Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk ---(end of broadcast)--- TIP 8: explain analyze is your friend -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-222-2783 - [EMAIL PROTECTED] - http://www.commandprompt.com Editor-N-Chief - PostgreSQl.Org - http://www.postgresql.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Proposal for a cascaded master-slave replication system
In the last exciting episode, [EMAIL PROTECTED] (Jan Wieck) wrote:
> I look forward to your comments.
It is not evident from the paper what approach is taken to dealing
with the duplicate key conflicts.
The example:
UPDATE table SET col1 = 'temp' where col = 'A';
UPDATE table SET col1 = 'A' where col = 'B';
UPDATE table SET col1 = 'B' where col = 'temp';
I can think of several approaches to this:
1. The present eRserv code reads what is in the table at the time of
the 'snapshot', and so tries to pass on:
update table set col1 = 'B' where otherkey = 123;
update table set col1 = 'A' where otherkey = 456;
which breaks because at some point, col1 is not unique, irrespective
of what order we apply the changes in.
2. If the contents as at the time of the COMMIT are stored in the log
table, then we would do all three updates in the destination DB, in
order, as shown above.
Either we have to:
a) Store the updated fields in the replication tables somewhere, or
b) Make the third UPDATE wait for the updates to be stored in a
file somewhere.
3. The replication code requires that any given key only be updated
once in a 'snapshot', so that the updates may be unambiguously
partitioned:
UPDATE table SET col1 = 'temp' where col = 'A' ; -- and otherkey = 123
UPDATE table SET col1 = 'A' where col = 'B'; -- and otherkey = 456
-- Must partition here before hitting #123 again --
UPDATE table SET col1 = 'B' where col = 'temp'; -- and otherkey = 123
The third UPDATE may have to be held up until the "partition" is set
up, right?
4. I seem to recall a recent discussion about the possibility of
deferring the UNIQUE constraint 'til the END of a commit, with the
result that we could simplify to
update table set col1 = 'B' where otherkey = 123;
update table set col1 = 'A' where otherkey = 456;
and discover that the UNIQUE constraint was relaxed just long enough
for us to make the TWO changes that in the end combined to being
unique.
None of these look like they turn out totally happily, or am I missing
an approach?
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','ntlug.org').
http://www.ntlug.org/~cbbrowne/languages.html
"Java and C++ make you think that the new ideas are like the old ones.
Java is the most distressing thing to hit computing since MS-DOS."
-- Alan Kay
---(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
[GENERAL] embedded postgresql
Hi, I am starting a new project where I intend to use embedded database server in my win32 application. I intend to use VC++ microsoft studio 6.0 as my development environment. The postgres.org website seems to be catering for people with all sorts of requirements and platforms. 1. Where can I find the binary distribution so that I can use postgres server as an embedded database. I assume it is a .dll 2. Also I would like to have the documentation which shows how to start & stop the server programmatically and execute sql, to create a database, use select insert , update etc. 3. Is it correct that I do not have to pay any user licences. Postgres is free not only for development but also I can include it in my software package. Thanks. Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] procpid in pg_stat_activity but no correspoding backend
Rajesh Kumar Mallah wrote: Hi , I noticed that an entry in pg_stat_activity was existing for a long duration > 2 mins while its backend ( process corresponding to that procpid) was not running. how can it be possible ? version: 7.3.4 There was a bug that caused a crashing backend to hang around in the stats until the slot is reused, because the message telling that the backend terminates is never sent to the collector daemon. I'm not sure if that got fixed for 7.4. 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 7: don't forget to increase your free space map settings
Re: [GENERAL] embedded postgresql
It is a shame that postgres is not available as an embedded server unlike mysql database server which comes in the form of a dll. However with mysql licence I would have to pay $10,000 if I wish to include it in mysql as an embedded server in my app. mysql database server is also a TCP/IP socket server whether embedded or otherwise. --- "Joshua D. Drake" <[EMAIL PROTECTED]> wrote: > jini us wrote: > > >Hi, > > > >I am starting a new project where I intend to use > >embedded database server in my win32 application. > >I intend to use VC++ microsoft studio 6.0 as my > >development environment. > > > >The postgres.org website seems to be catering for > >people with all sorts of requirements and > platforms. > > > >1. Where can I find the binary distribution so that > I > >can use postgres server as an embedded database. > >I assume it is a .dll > > > > > No... PostgreSQL is a database server, thus you > connect to it via TCP/IP > or domain sockets (preferrably TCP/IP). > > Also an emulation layer such as Cygwin will be > required to use PostgreSQL > on Windows. > > >2. Also I would like to have the documentation > which > >shows how to start & stop the server > programmatically > >and execute sql, to create a database, use select > >insert , update etc. > > > > > > > www.postgresql.org click on docs. > > > >3. Is it correct that I do not have to pay any user > >licences. > >Postgres is free not only for development but also > I > >can include it in my software package. > > > > > > > Yes that is correct. > > > >Thanks. > > > > > > > > > > > > > > > > > > > >Want to chat instantly with your online friends? > Get the FREE Yahoo! > >Messenger http://mail.messenger.yahoo.co.uk > > > >---(end of > broadcast)--- > >TIP 8: explain analyze is your friend > > > > > > > -- > Command Prompt, Inc., home of Mammoth PostgreSQL - > S/ODBC and S/JDBC > Postgresql support, programming shared hosting and > dedicated hosting. > +1-503-222-2783 - [EMAIL PROTECTED] - > http://www.commandprompt.com > Editor-N-Chief - PostgreSQl.Org - > http://www.postgresql.org > > > > ---(end of > broadcast)--- > TIP 4: Don't 'kill -9' the postmaster Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Curious about exclusive table locks
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi. I have the following little stored proc: CREATE OR REPLACE FUNCTION public.ib_nextval(varchar) RETURNS varchar AS 'DECLARE countername ALIAS FOR $1; cprefix varchar; counter integer; dlen integer; complete varchar; format varchar; BEGIN LOCK TABLE ib_counter IN ACCESS EXCLUSIVE MODE; SELECT INTO cprefix,counter,dlen prefix,last_value,display_length FROM ib_counter WHERE name=countername; counter := counter + 1; UPDATE ib_counter SET last_value=counter WHERE name=countername; format := \'FM\'; FOR i IN 1..dlen LOOP format := format || \'0\'; END LOOP; complete := cprefix || to_char(counter,format); RAISE NOTICE \'result is %,%,%,%\',complete,cprefix,counter,dlen; RETURN complete; END; It's basically a counter incremental thing that is independant from any serial value, but it behaves like a serial.So everytime the func is called, it increments a counter and returns the new "key". This works nice and throws no errors, however the line LOCK TABLE ib_counter IN ACCESS EXCLUSIVE MODE; seems to be ignored, since it's possible to create the same counter twice when the func is run twice at virtually the same time. To my understanding the access exclusive mode should lock the table for read access also, so it should be impossible to get the same result twice. (btw the result looks like ABC-123) Why doesn't this lock the table for read ? Thx UC - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE/svHPjqGXBvRToM4RApv/AJ9BrDgWVYmFahr0dUJ1kxbJpbjzkQCgvhfW 9sv+WWSlOuf8+FZA/F9nD/c= =Cl1k -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] serial type vs. sequences
I was wondering if there was a reliable method to get the last id inserted into a serial column in a table. I believe previous recommendations given were to use a sequence, have your app get the nextval for the sequence and then use that in the insert. I was wondering if things had changed or if that is still the recommended method. -- Suchandra Thapa <[EMAIL PROTECTED]> signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Perfomance difference between 7.2 and 7.3
Paulo Jan <[EMAIL PROTECTED]> writes: > -> Seq Scan on todocinetv (cost=0.00..37768.90 rows=28792 > width=8) The estimated cost seems to be more than one disk page read per row returned. This suggests to me that you have a huge amount of dead space in that table --- try a VACUUM FULL on it. If that fixes the problem, then you need to improve your housekeeping procedures on the 7.2 installation: run vacuums more often and ensure that your FSM settings are large enough. regards, tom lane ---(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: [GENERAL] Problem with FKEYS
Tom Lane wrote: Rajesh Kumar Mallah <[EMAIL PROTECTED]> writes: How come pgsql7.3.4 Was allowing me to delete the master record while referencing records were present in slave table? The only explanation I can think of is that the referencing row shown as being in user_services was actually in a child table --- foreign keys don't work in inheritance hierarchies at the moment. No, That is not the case , they are plain tables. If that's not it, can you provide a self-contained example? Could not replicate it with newly created tables. Only those set of table has the problem. I am ready to provide any required info . Regds Mallah. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Column Sizes
Hi NG, How do I find out the size of a column ? I am retrieving large objects from the pg_largeobject table and creating a files, I would like to know the length of the data column so that I can implement buffering instead of writing hundreds of small chunks to disk. Any help would be appreciated. Thanks in advance, Brett ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Perfomance difference between 7.2 and 7.3
Hi all: I have here a table with the following schema: Table "todocinetv" Column|Type | Modifiers -+-+-- id | integer | not null default '0' datestamp | timestamp without time zone | not null thread | integer | not null default '0' parent | integer | not null default '0' author | character(37) | not null default '' subject | character(255) | not null default '' email | character(200) | not null default '' attachment | character(64) | default '' host| character(50) | not null default '' email_reply | character(1)| not null default 'N' approved| character(1)| not null default 'N' msgid | character(100) | not null default '' modifystamp | integer | not null default '0' userid | integer | not null default '0' Indexes: todocinetv_approved, todocinetv_author, todocinetv_datestamp, todocinetv_modifystamp, todocinetv_msgid, todocinetv_parent, todocinetv_subject, todocinetv_thread, todocinetv_userid, todocinetvpri_key (It's actually a table created by the discussion board application Phorum (version 3.3)). This table has about 28000 rows, and is running with Postgres 7.2.3 under Red Hat 8.0, in a 2.4 Ghz. Pentiun 4 with 512 Mb. of RAM. The problem I'm having is that, when you access the main page of the discussion board, it takes forever to show you the list of posts. The query that Phorum uses for doing so is: phorum=# explain phorum-# SELECT thread, modifystamp, count(id) AS tcount, datetime(modifystamp) AS latest, max(id) as maxid FROM todocinetv WHERE approved='Y' GROUP BY thread, modifystamp ORDER BY modifystamp desc, thread desc limit 30; NOTICE: QUERY PLAN: Limit (cost=40354.79..40354.79 rows=30 width=12) -> Sort (cost=40354.79..40354.79 rows=2879 width=12) -> Aggregate (cost=39901.43..40189.35 rows=2879 width=12) -> Group (cost=39901.43..40045.39 rows=28792 width=12) -> Sort (cost=39901.43..39901.43 rows=28792 width=12) -> Seq Scan on todocinetv (cost=0.00..37768.90 rows=28792 width=12) This query takes up to 3 minutes to execute. I have tried to strip it down and leaving it in its most vanilla form (without "count(id)" and such), and it's still almost as slow: phorum=# explain phorum-# SELECT thread, modifystamp, datetime(modifystamp) AS latest from todocinetv WHERE approved='Y' ORDER BY modifystamp desc, thread desc limit 30; NOTICE: QUERY PLAN: Limit (cost=39901.43..39901.43 rows=30 width=8) -> Sort (cost=39901.43..39901.43 rows=28792 width=8) -> Seq Scan on todocinetv (cost=0.00..37768.90 rows=28792 width=8) But here is the weird thing: I dump the table, export it into another machine running Postgres 7.3.2 (Celeron 1.7 Ghz, 512 Mb. of memory), and the query takes only 2 or 3 seconds to execute, even though the query plan is almost the same: provphorum=# explain provphorum-# SELECT thread, modifystamp, count(id) AS tcount, modifystamp AS latest, max(id) as maxid FROM todocinetv WHERE approved='Y' GROUP BY thread, modifystamp ORDER BY modifystamp desc, thread desc limit 30 ; QUERY PLAN Limit (cost=5765.92..5765.99 rows=30 width=12) -> Sort (cost=5765.92..5772.96 rows=2817 width=12) Sort Key: modifystamp, thread -> Aggregate (cost=5252.34..5604.49 rows=2817 width=12) -> Group (cost=5252.34..5463.63 rows=28172 width=12) -> Sort (cost=5252.34..5322.77 rows=28172 width=12) Sort Key: thread, modifystamp -> Seq Scan on todocinetv (cost=0.00..3170.15 rows=28172 width=12) Filter: (approved = 'Y'::bpchar) (9 rows) (I took out the "datetime" function, since 7.3 didn't accept it and I didn't think it was relevant to the performance problem (am I wrong?)) So my question is: what causes such a big difference? (3 min. vs. 3 seconds) Does the version difference (7.2 vs. 7.3) account for all of it? Or should I start looking at other factors? As I said, both machines are almost equivalent hardware-wise, and as for the number of shared buffers, the faster machine actually has less of them (the 7.3 machine has "shared_buffers = 768", while the 7.2 one has "shared_buffers = 1024"). Paulo Jan. DDnet. ---(e
Re: [GENERAL] More Praise for 7.4RC2
Actually, in your case it's probably the new optimisation regarding the use of IN (subquery). They're now optimised to the same lavel as EXISTS IIRC. On Wed, Nov 12, 2003 at 05:46:23PM -0800, Reece Hart wrote: > On Wed, 2003-11-12 at 09:04, jake johnson wrote: > > > I also posted about the performance increase of 7.4, but I think that > > much of the difference you're seeing (because it's such a large > > difference) is probably due to the cleanliness of a newly restored > > database from backup. > > > I agree that this seems likely, except that the 7.3.4 database is > vacuumed nightly, and analyzed periodically. And about a week ago I > reclustered on the index intended to most facilitate this select. > Furthermore, merely hardcoding the subselect result achieves a > tremendous improvement (which was the workaround I used). So, I'm pretty > sure that it's not a vacuum, index use, or cleanliness issue. > > I also meant to add in my original post that the system is a dual 2.4G > xeon with 4GB of RAM. > > -Reece > > > > -- > Reece Hart, http://www.in-machina.com/~reece/, GPG:0x25EC91A0 0xD178AAF9 -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > "All that is needed for the forces of evil to triumph is for enough good > men to do nothing." - Edmond Burke > "The penalty good people pay for not being interested in politics is to be > governed by people worse than themselves." - Plato pgp0.pgp Description: PGP signature
Re: [GENERAL] simple question
Is this correct? vacuum by itself just cleans out the old extraneous tuples so that they aren't in the way anymore vacuum analyze rebuilds indexes. If you add an index to a table it won't be used until you vacuum analyze it vacuum full actually compresses the table on disk by reclaiming the space from the old tuples after they have been removed. - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Paulo Jan" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, November 12, 2003 8:38 AM Subject: Re: [GENERAL] Perfomance difference between 7.2 and 7.3 > Paulo Jan <[EMAIL PROTECTED]> writes: > > -> Seq Scan on todocinetv (cost=0.00..37768.90 rows=28792 > > width=8) > > The estimated cost seems to be more than one disk page read per row > returned. This suggests to me that you have a huge amount of dead space > in that table --- try a VACUUM FULL on it. If that fixes the problem, > then you need to improve your housekeeping procedures on the 7.2 > installation: run vacuums more often and ensure that your FSM settings > are large enough. > > regards, tom lane > > ---(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 > ---(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: [GENERAL] multibyte support
Ma Siva Kumar <[EMAIL PROTECTED]> writes:
> On Tuesday 11 Nov 2003 9:02 pm, Dennis Gearon wrote:
>> This is something I've been wondereing about for quite awhile - does
>> pgsql measure bytes or chars when using UTF for varchars. It looks like
>> bytes, which is counter intuitive.
The measurement is certainly in characters, in 7.3 and later. In 7.2 it
was in characters if you'd enabled multibyte. Once upon a time it was
in bytes, but I don't believe that applies to Ma Siva Kumar's problem.
> 在您的系统中直接获 (entered through html form processed by php script)
> shows as
> 在您的系统 when seen with psql. Anything more
> than this is rejected for lack of space (the size is varchar(100)
I think there is some confusion between you and the database about
character set encoding. Double check what the database encoding is
(psql \l will tell you). And double check what the system thinks the
client-side encoding is ("show client_encoding" and/or \encoding).
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Column Sizes
On Nov 12, 2003, at 11:04 AM, Brett Maton wrote: Hi NG, How do I find out the size of a column ? I am retrieving large objects from the pg_largeobject table and creating a files, I would like to know the length of the data column so that I can implement buffering instead of writing hundreds of small chunks to disk. Any help would be appreciated. Thanks in advance, Brett I always check for the size this way: select sum(length(data)) from pg_largeobject where loid = xxx; Or if you want to know the size of an individual page: select pageno, length(data) from pg_largeobject where loid = xxx order by pageno; Hope this help, Adam Ruth ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] plpgsql return setof integer?
Greetings,
I've got a fairly simple function that I'm trying to return a set, but
seem to have come across a stumbling block. When I execute the function
below (the input params are not used at the moment) I get this:
chris=# select htdig('foo', 'foo');
ERROR: set-valued function called in context that cannot accept a set
CONTEXT: PL/pgSQL function "htdig" line 14 at return next
CREATE OR REPLACE FUNCTION htdig(text, text) RETURNS SETOF integer AS '
DECLARE
result text[];
low integer;
high integer;
item integer;
BEGIN
result := htsearch(''sample_return.txt'',''dbname'');
low := 2;
high := array_upper(result, 1);
FOR i IN low..high LOOP
item := result[i];
RETURN NEXT item;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql' STABLE STRICT;
The function htsearch is working as expected (other than a strange HASH
as the first element, but I've compensated for that by starting at array
index 2). This is what it outputs:
chris=# select htsearch('sample_return2.txt','dbname');
htsearch
--
{HASH(0x835c298),2100,2113,2114}
(1 row)
Any obvious thing that I'm doing wrong? I'm using 7.4RC2. As always, any
help or info would be much appreciated. Bonus points if someone knows
what the HASH is. :-)
Cheers,
Chris
--
Christopher Murtagh
Enterprise Systems Administrator
ISR / Web Communications Group
McGill University
Montreal, Quebec
Canada
Tel.: (514) 398-3122
Fax: (514) 398-2017
---(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: [GENERAL] SQL-question: returning the id of an insert querry
Scott Chapman <[EMAIL PROTECTED]> writes: > It would be nice if PostgreSQL could return the primary key it inserted > with but that may not be a fool-proof solution either. Is there a nice > way to handle this situation? Write a database function that inserts the record and returns the primary key value? That's probably the best way to insulate your app from the database structure... -Doug ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] SQL-question: returning the id of an insert querry
Scott Chapman <[EMAIL PROTECTED]> writes:
> On Wednesday 12 November 2003 11:29, Doug McNaught wrote:
> > Scott Chapman <[EMAIL PROTECTED]> writes:
> > > It would be nice if PostgreSQL could return the primary key it
> > > inserted with but that may not be a fool-proof solution either. Is
> > > there a nice way to handle this situation?
> >
> > Write a database function that inserts the record and returns the
> > primary key value? That's probably the best way to insulate your app
> > from the database structure...
>
> The function still has to know which sequence to pull from doesn't it?
Yes. It's theoretically possible to derive that information if you
have enough system-tables-fu, but since the function knows which
table it's inserting into, it's not hard to put the proper sequence
name in as well.
> I don't know much about triggers/functions in PG. Is it possible to
> have a function that intercepts the information AFTER the sequence
> value is added as the new primary key and then return it? This would
> enable the use of a more generic function.
Sure, in the function you would basically do (I forget the exact
pl/pgsql syntax):
INSERT INTO foo VALUES (...);
SELECT currval('the_pk_sequence') INTO pk;
RETURN pk;
Doesn't remove the need to know or derive the proper sequence name.
There is no "what primary key did I just insert" built into PG. And
you will need a separate function for each table.
But this way the DB knowledge resides in the DB and you just have a
nice clean API for inserting data from the clients. The schema can
change and the API will (homefully) remain the same...
-Doug
---(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: [GENERAL] procpid in pg_stat_activity but no correspoding backend
Jan Wieck wrote: Rajesh Kumar Mallah wrote: Hi , I noticed that an entry in pg_stat_activity was existing for a long duration > 2 mins while its backend ( process corresponding to that procpid) was not running. how can it be possible ? version: 7.3.4 There was a bug that caused a crashing backend to hang around in the stats until the slot is reused, because the message telling that the backend terminates is never sent to the collector daemon. I'm not sure if that got fixed for 7.4. Thanks for explaining. Regds Mallah. Jan ---(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: [GENERAL] Proposal for a cascaded master-slave replication system
Christopher Browne wrote: In the last exciting episode, [EMAIL PROTECTED] (Jan Wieck) wrote: I look forward to your comments. It is not evident from the paper what approach is taken to dealing with the duplicate key conflicts. The example: UPDATE table SET col1 = 'temp' where col = 'A'; UPDATE table SET col1 = 'A' where col = 'B'; UPDATE table SET col1 = 'B' where col = 'temp'; I can think of several approaches to this: One fundamental flaw in eRServer is that it tries to "combine" multiple updates into one update at snapshot-time in the first place. The application can do these three steps in one single transaction, how do you split that? You can develop an automatic recovery for that. At the time you got a dupkey error, you rollback but remember the _rserv_ts and table_id that caused the dupkey. In the next sync attempt, you fetch the row with that _rserv_ts and delete all rows from the slave table with that primary key plus fake INSERT log rows on the master for the same. Then you prepare and apply and cross fingers that nobody touched the same row again already between your last attempt and now ... which was how many hours ago? And since you can only find one dupkey per round, you might do this a few times with larger and larger lists of _rserv_ts,table_id. The idea of not accumulating log forever, but just holding this status table (the name log is misleading in eRServer, it holds flags telling "the row with _rserv_ts= got INS|UPD|DEL'd") has one big advantage. However long your slave does not sync, your master will not run out of space. But I don't think that there is value in the attempt to let a slave catch up the last 4 days at once anyway. Drop it and use COPY. When your slave does not come up before you have modified half your database, it will be faster this way anyway. Jan 1. The present eRserv code reads what is in the table at the time of the 'snapshot', and so tries to pass on: update table set col1 = 'B' where otherkey = 123; update table set col1 = 'A' where otherkey = 456; which breaks because at some point, col1 is not unique, irrespective of what order we apply the changes in. 2. If the contents as at the time of the COMMIT are stored in the log table, then we would do all three updates in the destination DB, in order, as shown above. Either we have to: a) Store the updated fields in the replication tables somewhere, or b) Make the third UPDATE wait for the updates to be stored in a file somewhere. 3. The replication code requires that any given key only be updated once in a 'snapshot', so that the updates may be unambiguously partitioned: UPDATE table SET col1 = 'temp' where col = 'A' ; -- and otherkey = 123 UPDATE table SET col1 = 'A' where col = 'B'; -- and otherkey = 456 -- Must partition here before hitting #123 again -- UPDATE table SET col1 = 'B' where col = 'temp'; -- and otherkey = 123 The third UPDATE may have to be held up until the "partition" is set up, right? 4. I seem to recall a recent discussion about the possibility of deferring the UNIQUE constraint 'til the END of a commit, with the result that we could simplify to update table set col1 = 'B' where otherkey = 123; update table set col1 = 'A' where otherkey = 456; and discover that the UNIQUE constraint was relaxed just long enough for us to make the TWO changes that in the end combined to being unique. None of these look like they turn out totally happily, or am I missing an approach? -- #==# # 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 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: [GENERAL] SQL-question: returning the id of an insert querry
"scott.marlowe" <[EMAIL PROTECTED]> writes:
> select tablename.fieldname.currval;
That syntax would be problematic, it would mean to select all rows from
tablename and evaluate fieldname.currval for each one. Actually it's worse, it
would be confused with schemas I think.
The postgres-ish way to do this would be to create a function like currval
that took a table and column and told you the currval of the sequence
associated with it.
Well you can already do something like that:
db=> create or replace function currval(text,text) returns bigint as 'select
currval($1 || ''_'' || $2 || ''_seq'')' language sql strict;
CREATE FUNCTION
db=> create table test (a serial);
NOTICE: CREATE TABLE will create implicit sequence "test_a_seq" for "serial" column
"test.a"
CREATE TABLE
db=> insert into test(a) values (default);
INSERT 14080230 1
db=> select currval('test','a');
currval
-
1
(1 row)
The only problem arises if you use table names or column names that cause
postgres to truncate the resulting sequence name. This could be worked-around
by using the dependency information instead of depending on the naming scheme.
But as long as you do that the above works fine. And means you could always
change your naming scheme or method for looking up the associated sequence
later without changing all your sql.
--
greg
---(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: [GENERAL] how can I change a btree index into a hash index?
Mark Harrison <[EMAIL PROTECTED]> writes: > Since these are all unique things, and will only be tested for > equality, I am guessing that making a hash index will be better > than making a btree index. You are mistaken. If there were any real value in that, we'd offer an easier way to do it. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] plperl/createlang issue
On Tue, 2003-11-11 at 21:22, Ed L. wrote: > $ createlang plperl template1 > ERROR: Load of file /opt/pgsql/installs/postgresql-7.3.4/lib/plperl.so > failed: libperl.so: cannot open shared object file: No such file or > directory > createlang: language installation failed I had the exact problem recently, and I found the solution. 1) First, locate your libperl.so file. On my PPC box it was: [EMAIL PROTECTED] chris]$ locate libperl.so /usr/lib/perl5/5.8.0/ppc-linux-thread-multi/CORE/libperl.so 2) Add that directory to /etc/ld.so.conf 3) run ldconfig (as root) Then your creatlang statement should work. Hope that helps. Cheers, Chris > But it sure looks like its there to me: > > $ ls -l /opt/pgsql/installs/postgresql-7.3.4/lib/plperl.so > -rwxr-xr-x1 pg pg 35770 Nov 11 19:39 > /opt/pgsql/installs/postgresql-7.3.4/lib/plperl.so* > > I *think* my perl is threaded based on perl -v: > > $ perl -v > > This is perl, v5.8.0 built for i386-linux-thread-multi > ... > > Any clues for the clueless? > > TIA. > > Ed > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend ---(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
[GENERAL] Problem with FKEYS
How come pgsql7.3.4 Was allowing me to delete the master record while referencing records were present in slave table? The problem was detected when the database was migrated to 7.4 and deletions were being refused. Regards Mallah tradein_clients=# \d user_services Table "public.user_services" Column | Type | Modifiers +-+--- userid | integer | not null service_id | integer | not null Indexes: user_services_key unique btree (userid, service_id), user_services_service_id btree (service_id) Foreign Key constraints: $2 FOREIGN KEY (userid) REFERENCES users(userid) ON UPDATE NO ACTION ON DELETE NO ACTION, $1 FOREIGN KEY (service_id) REFERENCES services_master(service_id) ON UPDATE NO ACTION ON DELETE NO ACTION tradein_clients=# SELECT * from user_services where userid=276720 ; userid | service_id + 276720 | 1 (1 row) tradein_clients=# begin work;DELETE from users where userid=276720 ;rollback; BEGIN DELETE 1 ROLLBACK tradein_clients=# SELECT version(); version - PostgreSQL 7.3.4 on i686-pc-linux-gnu, compiled by GCC 2.96 (1 row) tradein_clients=# ---(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: [GENERAL] SQL-question: returning the id of an insert querry
On Mon, Nov 10, 2003 at 08:56:03AM -0800, Scott Chapman wrote: > I talked with the author or SQLObject about this recently and I thnk > he's implementing this correctly, by querying the cursor for the last > OID?: That won't scale unless you index oid. And your tables will all need oids, which is not standard any more. If you do your work in one transaction and get the currval that way, it is impossible to go wrong. Also, if you don't return the connection to the pool before getting the currval, you will not go wrong. A -- Andrew Sullivan 204-4141 Yonge Street Afilias CanadaToronto, Ontario Canada <[EMAIL PROTECTED]> M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] multibyte support
On Tuesday 11 Nov 2003 9:02 pm, Dennis Gearon wrote: > This is something I've been wondereing about for quite awhile - does > pgsql measure bytes or chars when using UTF for varchars. It looks like > bytes, which is counter intuitive. What are the byte codes for those 15 > chars. I think the maximum UTF char's byte lenghty is either 5 or 6 > bytes.. Since there are SO many chinese people in the world and Chinese > should either be popluar or getting popular in the comptuer world, I > would have though thta the UTF consotium wold have made Chinese at a > point in the tables that it only required 2,3. or 4 bytes max, and made > obtuse languages up in the 5 to 6 byte part of the table. åæçççäçæè (entered through html form processed by php script) shows as 在您的系统 when seen with psql. Anything more than this is rejected for lack of space (the size is varchar(100) If someone can throw more light on this, I will be grateful. Best regards -- Integrated Management Tools for leather industry -- http://www.leatherlink.net Ma Siva Kumar, BSG LeatherLink (P) Ltd, ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] RHEL
Adam Haberlach <[EMAIL PROTECTED]> writes: > I was, a few minutes ago, stunned to discover that as far as I can > tell, the postgres server is not part of Red Hat Server ES Feel free to let Red Hat know that you're unhappy about this. (Not totally unbiased here ... I'm getting *very* tired about RH's internal indecision about their extent of commitment to Postgres. I think frequent whacks-upside-the-head from paying customers may be the only way to get upper management to sit up and take notice.) regards, tom lane ---(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
