[GENERAL] Documentation of the Front End/Back End Protocol for Large Objects
Hi all. I would like to study the protocol for large object operations (lo_read, lo_write,...) between the front-end and back-end. I've found this http://www.postgresql.org/docs/8.1/static/protocol.html, but it looks like it has no message specific for large object... How is it implemented in client (libpq, I suppose)? Regards Marco -- Marco Bizzarri http://notenotturne.blogspot.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Plperl and my() lexical variables bug?
Hi,
I have something strange here, with Postgresql 8.1.4 under Linux ES 4,
installed from the PG Group binaries:
If I run this script:
CREATE OR REPLACE FUNCTION foo() RETURNS void
AS
$$
my $val;
sub init
{
$val = @_[0];
elog(NOTICE, 1: @_[0]\n);
}
init(12);
elog(NOTICE, 2: $val\n);
$$
LANGUAGE 'plperl';
select * from foo();
I get in return something correct:
NOTICE: 1: 12
NOTICE: 2: 12
Total query runtime: 63 ms.
Data retrieval runtime: 62 ms.
1 rows retrieved.
But then, if I simply call the function, with:
select * from foo();
I get:
NOTICE: 1: 12
NOTICE: 2:
Total query runtime: 63 ms.
Data retrieval runtime: 62 ms.
1 rows retrieved.
$val variable is missing.
Even more strange: if I replace my $val; with $val;, this does not happen
at all:
CREATE OR REPLACE FUNCTION foo() RETURNS void
AS
$$
$val;
sub init
{
$val = @_[0];
elog(NOTICE, 1: @_[0]\n);
}
init(12);
elog(NOTICE, 2: $val\n);
$$
LANGUAGE 'plperl';
Now I can call the function with
select * from foo();
it works as expected:
NOTICE: 1: 12
NOTICE: 2: 12
Total query runtime: 390 ms.
Data retrieval runtime: 797 ms.
1 rows retrieved.
Am I missing something maybe? It sounds like a bug with lexical variables to
me...
Cheers,
--
Philippe Lang, Ing. Dipl. EPFL
Attik System
rte de la Fonderie 2
1700 Fribourg
Switzerland
http://www.attiksystem.ch
Tel: +41 (26) 422 13 75
Fax: +41 (26) 422 13 76
smime.p7s
Description: S/MIME cryptographic signature
Re: [GENERAL] minimizing downtime when upgrading
Jim Nasby wrote: On Jun 21, 2006, at 7:42 AM, H.J. Sanders wrote: The last 15 years we also used Informix and we never, never had to unload/load the database because of an upgrade. Perhaps somebody knows how they do the trick? Do they provide a migration/upgrade utility? In the case of MS SQL Server the issue doesn't come up. Here are some user experiences: 1) Create a database in MS SS 6 2) Upgrade software to MS SS 7 3) Use software 1) Create a database in MS SS 6 2) Backup database (closest analog is -Fc option of pg_dump) 3) Move backup to machine running MS SS 7 4) Attempt to restore. Program warns it will upgrade database and you can't go back, proceed? You say yes. begin:vcard fn:Kenneth Downs n:Downs;Kenneth email;internet:[EMAIL PROTECTED] tel;work:631-689-7200 tel;fax:631-689-0527 tel;cell:631-379-0010 x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Plperl and my() lexical variables bug?
On Fri, Jun 23, 2006 at 11:33:42AM +0200, Philippe Lang wrote:
Am I missing something maybe? It sounds like a bug with lexical variables to
me...
I think what's happening is that sub init is created once with $val
referencing the lexically-scoped $val from sub foo's first invocation.
When you call foo again, foo creates a new lexically-scoped $val
but init's $val still refers to the object from foo's first call.
You can see this if you display \$val:
CREATE OR REPLACE FUNCTION foo() RETURNS void AS $$
my $val;
sub init {
$val = $_[0];
elog(NOTICE, 1: $_[0] . \$val);
}
init(12);
elog(NOTICE, 2: $val . \$val);
$$ LANGUAGE plperl;
SELECT foo();
NOTICE: 1: 12 SCALAR(0x8447220)
NOTICE: 2: 12 SCALAR(0x8447220)
foo
-
(1 row)
SELECT foo();
NOTICE: 1: 12 SCALAR(0x8447220)
NOTICE: 2: SCALAR(0x83f5c4c)
foo
-
(1 row)
This behavior isn't specific to PL/Perl. A standalone Perl program
exhibits the same behavior, so you might find a better explanation
in a Perl-specific forum like the comp.lang.perl.misc newsgroup.
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] JUST NOT ADDING UP
Rhys Stewart wrote: hi all, something is not adding up. the following query is taking a long time to run.(its still running right now) select distinct on (prem) prem, num, addy, mynum,myad,ff.address, matchtype, the_geom from daily.recheck2, _sp_myparcels ff where prem not in ( select distinct on (prem) prem from daily.recheck2 dr, _sp_myparcels ff where ff.address = unabrev ) AND btrim(addy) = btrim(myad) AND num = mynum You're probably better of with a NOT EXISTS here, instead of a NOT IN. UNION And a UNION ALL here, considering you're already using distinct. Or you could remove the distincts, maybe. select distinct on (prem) prem, num, addy, mynum,myad,dr.unabrev, matchtype, ff.the_geom from daily.recheck2 dr, _sp_myparcels ff--, feeder_polygon where ff.address = unabrev You could have made your query a bit more readable. For example, leaving the commented out feeder_polygon attribute there could confuse people who're used to a -- (decrement) operator from other languages. the thing is if i run the first query by itself, it takes like about 2 seconds, and if i run the subquery that takes about 2 seconds also, so why (well its now finished, took all of 3.31 minutes) does it take so long? Why do you ask us, instead of the database? EXPLAIN ANALYZE is your (and our) friend. Without that we can only guess what's slowing down your query. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Plperl and my() lexical variables bug?
On Fri, Jun 23, 2006 at 07:49:19AM -0600, Michael Fuhr wrote:
This behavior isn't specific to PL/Perl. A standalone Perl program
exhibits the same behavior, so you might find a better explanation
in a Perl-specific forum like the comp.lang.perl.misc newsgroup.
If you run it standalone with warnings enabled, you get this:
Variable $val will not stay shared at a.pl line 6.
Which is pretty much what is happening. There's plenty written about
this on the web. This has a good summary as well as solutions:
http://perl.com/pub/a/2002/05/07/mod_perl.html
In particular, if you use diagnostics it suggests an anonymous sub
will deal with it.
Hope this helps,
a.pl
use warnings;
use diagnostics;
sub test
{
my $val;
sub init {
$val = $_[0];
print( 1: $_[0] . \$val . \n);
}
init(12);
print(2: $val . \$val . \n);
}
test;
test;
--- snip ---
--
Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/
From each according to his ability. To each according to his ability to
litigate.
signature.asc
Description: Digital signature
Re: [GENERAL] Return the primary key of a newly inserted row?
SQL Server had a nifty feature here. You could simply toss a SELECT statement at the end of a trigger of sproc and the results would be returned. This in effect made a table the potential return type of all commands, which could be exploited very powerfully. Do the hackers have any thoughts along those lines? It's also a for instance where inline creation of variables is useful. As in: select id1 = nextval(somesequence) insert into tbl (id...) values (id1...) select id2 = nextval(somesequence) insert into tbl (id...) values (id2...) select id3 = nextval(somesequence) insert into tbl (id...) values (id3...) select id1, id2, id3; Or returning multiple result sets... insert into tbl (id...) values (nextval(somesequence)...) returning new.id; insert into tbl (id...) values (nextval(somesequence)...) returning new.id; insert into tbl (id...) values (nextval(somesequence)...) returning new.id; -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Documentation of the Front End/Back End Protocol for Large Objects
Marco Bizzarri [EMAIL PROTECTED] writes: I would like to study the protocol for large object operations (lo_read, lo_write,...) between the front-end and back-end. I've found this http://www.postgresql.org/docs/8.1/static/protocol.html, but it looks like it has no message specific for large object... libpq does it through Function Call messages that invoke lo_open and so on. Look into src/interfaces/libpq/fe-lobj.c. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] pg_dump With OIDs Supported?
Greetings everyone,Since we've started using the pg_autovacuum table we've come to realize that keeping OID values between our database dumps is critical. I've been doing some testing using the pg_dump command with the --oids option. For some reason, it doesn't seem like the the OID values are being dumped, because when I restore the dump into a different database I find that the OID values for my tables are different.As a test, I did the following: createdb testdb1 psql -c "create table mytest(words varchar)" testdb1 psql -c "select oid from pg_class where relname = 'mytest'" testdb1 oid = 52178917 pg_dump -f testdb.sql --oids testdb1 createdb testdb2 psql testdb2 testdb.sql psql -c "select oid from pg_class where relname = 'mytest'" testdb2 oid = 52178923 As you can see, the OID values are different in each database. Looking at the SQL dump I do not see any information related to OIDs.I also tried using pg_dump with the "-F c" and "-F t" parameter, using the pg_restore command and I see the same result. I've tested with PostgreSQL 8.1.3 on Mac OSX as well as PostgreSQL 8.1.4 on RHEL-3.Is this a bug or is this feature not supported anymore? Thanks for any input!--Dylan HansenEnterprise Systems Developer
Re: [GENERAL] Idea for vacuuming
I like to make sure the vacuum takes place during off peak times, which is why I don't use autovacuum. Jim Nasby wrote: On Jun 22, 2006, at 7:12 PM, Joseph Shraibman wrote: I'm running a 8.0 database. I have a very large log table that is rarely updated or deleted from. The nightly vacuum does not know this, and spends a lot of time on it, and all its indexes. My RFE: When vacuuming a table, pg should try to vacuum the primary key first. If that results in 0 recovered entries, then assume the table has no updates/deletes and skip the rest of that table. I'm picking the primary key here, but any index that indexes each row of the table will do. Maybe it should just pick the smallest index that indexes each row of the table. *shrug* It's kinda hard to get excited about that when running autovacuum (or pg_autovacuum in the case of 8.0) would be a much better solution. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 1: 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] Changing encoding of a database
We've PostgreSQL database, with SQL_ASCII or LATIN1 encoding. We would like to migrate them to UNICODE. Is there some contributed/available script, or this is something we should do at hand? I had a similar problem migrating from 7.4 to 8.1 and wanting to go from sql_ascii to utf8. I did the following: pg_dump -p 5433 --encoding ISO_8859_7 -t cas tj |psql tj where the dump connected to 7.4 (port 5433) and interpreted the cas data using ISO_8859_7. psql connected to 8.1 I had to experiment to find that ISO_8859_7 was the proper encoding - i had some greek (math and chemistry) letters which were accomodated by sql_ascii, but not quite properly. The output from pg_dump above properly converts to utf8 which 8.1 (i set the default enccoding utf8) accepts without complaint. See http://www.postgresql.org/docs/8.1/static/multibyte.html for all the other encodings. I don't think the above will convert a table in place, but could be used to create a copy with changed encoding. Hope this helps. TJ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pg_dump With OIDs Supported?
Dylan Hansen [EMAIL PROTECTED] writes: Since we've started using the pg_autovacuum table we've come to realize that keeping OID values between our database dumps is critical. I've been doing some testing using the pg_dump command with the --oids option. --oids is only meant to preserve OIDs within user tables; it never has and never will preserve OIDs for system-catalog entries. The real problem here is pg_autovacuum, which doesn't have any dump/restore support at the moment. This is because we stuck it into 8.1 at the last minute and aren't yet convinced it will survive in its current form. It strikes me that a relatively trivial hack would make it easier to dump and restore pg_autovacuum manually using COPY: change the declared type of the vacrelid column to regclass. This would make no difference to the internal use of the table, but it'd cause COPY to emit the column values in a symbolic format that would restore correctly. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Adding foreign key constraints without integrity
On 6/22/06 2:57 PM, Jim Nasby [EMAIL PROTECTED] wrote: If nothing else, you should bring it up on -hackers and ask to have this added as a TODO. It seems like a worth-while addition to pg_dump/ restore to me... Thanks for the suggestion. To answer another of your emails in this thread... a LAZY vacuum of the entire database will read every table and index in the database. Wanting to read the entire database to check for corruption is no reason to do a VACUUM FULL. Sorry, I misspoke. I do not do a VACUUM FULL. I do a VACUUM VERBOSE ANALYZE on a weekly basis. The verbose allows me to see which tables/indexes are getting bad (vacuum time wise). This is currently taking about 24 hours on a weekend (very little production load competing with it). The time drops dramatically after a reindex, then creeps up again as the indexes are updated in random order. Also, if you're curious about restore time for your upgrade, you should be doing the restore to an 8.1.4 database, not to your current version. There's been a ton of performance improvements made. In fact, I'm wondering if constraint checking in restore has been improved... Yes, that is what I did. I'm in the process of testing an upgrade from 7.3.x to 8.1.4 - export from 7.3.x and import into 8.1.4. Unfortunately, I'm sitting at about 90 hours when I've got about an 80 hour window on a long weekend... Wes ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pg_dump With OIDs Supported?
Hi Tom, thanks for your reply.On 23-Jun-06, at 11:17 AM, Tom Lane wrote:--oids is only meant to preserve OIDs within user tables; it never hasand never will preserve OIDs for system-catalog entries.So just to clarify, the table's OID itself will never be preserved, but the data inside the table will? Does each row have it's own OID? Pardon my n00b-ness on this question...The real problem here is pg_autovacuum, which doesn't have any dump/restoresupport at the moment. This is because we stuck it into 8.1 at the lastminute and aren't yet convinced it will survive in its current form.It strikes me that a relatively trivial hack would make it easier todump and restore pg_autovacuum manually using COPY: change the declaredtype of the vacrelid column to "regclass". This would make nodifference to the internal use of the table, but it'd cause COPY to emitthe column values in a symbolic format that would restore correctly.What I have done for the time being is created a script to be done that executes after every restore of the database that enters into pg_autovacuum based on the table name. For example:INSERT INTO pg_autovacuum (vacrelid, enabled, vac_base_thresh, vac_scale_factor, anl_base_thresh, anl_scale_factor, vac_cost_delay, vac_cost_limit) VALUES ((select oid from pg_class where relname = 'tablename'), true, 500, 0.1, 200, 0.05, -1, -1);This will work for now. It would be nice to have the vacrelid stay the same for each restore as the app we are currently using PostgreSQL with allows a script to be run before the dump is restored. I will just have to alter it to be run after the dump is restored.Thanks!--Dylan HansenEnterprise Systems Developer
Re: [GENERAL] Documentation of the Front End/Back End Protocol for Large Objects
Hi Tom. Thanks for your suggestion, this was my choice, after I was unable to find any reference. On 6/23/06, Tom Lane [EMAIL PROTECTED] wrote: Marco Bizzarri [EMAIL PROTECTED] writes: I would like to study the protocol for large object operations (lo_read, lo_write,...) between the front-end and back-end. I've found this http://www.postgresql.org/docs/8.1/static/protocol.html, but it looks like it has no message specific for large object... libpq does it through Function Call messages that invoke lo_open and so on. Look into src/interfaces/libpq/fe-lobj.c. regards, tom lane -- Marco Bizzarri http://notenotturne.blogspot.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Idea for vacuuming
Jim Nasby [EMAIL PROTECTED] writes: My RFE: When vacuuming a table, pg should try to vacuum the primary key first. If that results in 0 recovered entries, then assume the table has no updates/deletes and skip the rest of that table. That makes no sense. Vacuum starts by scanning the table itself, not the indexes. It only goes to the indexes after it has found tuples that need cleaning up. There's nothing to look at in the indexes that would tell it whether there are any tuples to clean up. -- greg ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Return the primary key of a newly inserted row?
Scott, Ken and Tim, Thanks for the assistance, I appreciate the advice. Scott, The example of select id1 = nextval(somesequence) could work for me. I have multiple users with our GUI and imagine I could use transaction protection to ensure no duplicates between selecting and incrementing the somesequence... Thanks again all. Regards John Scott Ribe wrote: SQL Server had a nifty feature here. You could simply toss a SELECT statement at the end of a trigger of sproc and the results would be returned. This in effect made a table the potential return type of all commands, which could be exploited very powerfully. Do the hackers have any thoughts along those lines? It's also a for instance where inline creation of variables is useful. As in: select id1 = nextval(somesequence) insert into tbl (id...) values (id1...) select id2 = nextval(somesequence) insert into tbl (id...) values (id2...) select id3 = nextval(somesequence) insert into tbl (id...) values (id3...) select id1, id2, id3; Or returning multiple result sets... insert into tbl (id...) values (nextval(somesequence)...) returning new.id; insert into tbl (id...) values (nextval(somesequence)...) returning new.id; insert into tbl (id...) values (nextval(somesequence)...) returning new.id; ---(end of broadcast)--- TIP 1: 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] VACUUM hanging on idle system
Tom Lane wrote: Clarence [EMAIL PROTECTED] writes: I have a completely idle postgresql system (all backends idle, none in transaction); every time I attempt to vacuum a particular table, it hangs after a while. ... In the system error log there is this line: could not write block 32756 of relation 1663/134097/385649401: No space left on device The referenced block is in the table's index file. The disk has plenty of space. ... but at one time not so much, right? At the time of the error, there was over 1000 gigabytes free. The least there has ever been was about 200 gigs free. Someone tried to drop the index and got this: ERROR: FlushRelationBuffers(idx_ld1, 0): block 32756 is referenced (private 0, global 1) What it looks like to me is that some backend tried to write out a dirty page, failed for lack of disk space, and neglected to release the buffer pin during error recovery. The extra pin would block VACUUM but not much of anything else. A postmaster restart should clear the problem. Yes, vacuuming was the only operation affected, and restarting did fix the problem. I seem to recall having fixed a bug like this in the past. What PG version are you running? We're running 8.0.3 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Form builder?
So far, here are the candidates: Andromeda, Lazarus, and Rekall. I was probably fairly inarticulate in my first post, but none of these seem to meet my criteria for automatic generation of forms based on the database definition. Most of the above frameworks have a good deal more functionality than I need, at least at first. Really I want to be able to open, say, ipython and type: Someobject.form(table='sometablename', times=3) (Maybe at the SQL prompt: \form name=name times=3) And have it give cycle three times through a reasonable (though possibly still imperfect) form for entering three rows in table sometablename. I don't want to do any developing except for finding out the table names in the database. I don't want to drag and drop forms into a visual editor and hook them up with procedures, and any extra processing should be done inside the database via triggers, defaults, etc (so the system would have to handle rollbacks and notices gracefully). Speed of data entry is the most important thing in the form and form chain itself. I have some ideas for chaining together forms when there are FK's, but I will talk about that later. I think it may be up to me at this point. Would anyone else find this useful? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] sql question; checks if data already exists before
nuno wrote: hi, there. i'm trying to write a SQL statement which does the following things. 1. checks if data already exists in the database 2. if not, insert data into database otherwise skip. Check this thread : http://archives.postgresql.org/pgsql-general/2005-10/msg01787.php ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] VACUUM hanging on idle system
Clarence [EMAIL PROTECTED] writes: Tom Lane wrote: I seem to recall having fixed a bug like this in the past. What PG version are you running? We're running 8.0.3 Um. Extract from the 8.0.5 CVS logs: 2005-12-08 14:19 tgl * src/backend/: postmaster/bgwriter.c, utils/resowner/resowner.c (REL8_0_STABLE): Fix bgwriter's failure to release buffer pins and open files after an error. This probably explains bug #2099 and could also account for mysterious VACUUM hangups. 8.0 branch is currently at 8.0.8 ... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
