[GENERAL] Get bytes sent to client
Hello. I'd like to collect some statistics in my application (using JDBC). In Oracle I could collect data, sent to client, using the following query: SELECT S.value FROM V$MYSTAT S,SYS.V_$STATNAME NAMES WHERE NAMES.STATISTIC#=S.STATISTIC# AND NAMES.NAME='bytes sent via SQL*Net to client'; Can I do something like this in PostgreSQL? I was suggested to wrap some parts of PostgreSQL JDBC Driver, but it looks not very good. Is there any other way to count up raw data, received by client (or sent by server)? The purpose is to estimate bandwidth from client to DBMS and size of query responses... -- Best regards, Alexander Pyhalov, system administrator of Computer Center of South Federal University -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Visibility of temporary database objects
Hi all, I have a simple question: Are the temporary database objects created within a pgplsql function visible/available outside the function (or implicit transaction) in any way. I am dropping and creating temporary database objects within a function and I am calling this function in from a threaded application which could use the same connection in more than one thread(is this possible/or even safe). I would like to avoid the possibility that the created temporary objects get dropped outside the current thread that created them. Allan. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Dependency tracking tool
Hello, In order to have a "global map" of the dependencies of the functions, views, tables in a PG database, I'd like to have some sort of "dependency tracking tool", that would show for each object: - which other objects depend on this object - on which other objects this object depends This would for example show that a table T1 has a trigger that depends on a trigger function F1 which itself depends on another function F2 that updates table T2, or that function F2 has a dependent trigger function F1 linked to a table T1, and that it updates table T2, etc... Is it correct to say that this "hierarchy" does not exist inside Postgresql, and that it is necessary to parse the INFORMATION_SCHEMA.* tables to get this information? Because of function overloading allowed in PG, I guess it is not an easy job. Does such a tool exist maybe? Best regards, Philippe --- Philippe Lang Web: www.attiksystem.ch Attik SystemEmail : philippe.l...@attiksystem.ch rte de la Fonderie 2Phone : +41 26 422 13 75 1700 Fribourg Mobile : +41 79 351 49 94 Switzerland Fax: +41 26 422 13 76 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to remove non-UTF values from a table?
An easy question for some I hope. I have a DB from 8.2 days that when I now dump and try to take into the 8.3.7, it gives me errors about utf-8 stuff. I tried searching this list's archives but could not come up with an answer. Google returns some sites like these: http://sniptools.com/databases/finding-non-utf8-values-in-postgresql - but I'm not clear on how to use them. Following the SQL on this site I could identify some columns that contain text like this: "Évolution générale de la situation démographique" So my guess is that the non-English characters were originally not getting written in proper utf-8 variants. Is there any SQL possibility to find these columns and replace them with utf-8 equivalents using some postgresql commands? Couldn't find anything in the "Strings functions" (chapter 9 of manual). We're on CentOS. Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pgAdmin III: timestamp displayed in what time zone?
Hi, I am using Postgres 8.3. I have a table defined like this: === -- Table: timeson -- DROP TABLE timeson; CREATE TABLE timeson ( id bigint NOT NULL, enddatetime timestamp without time zone NOT NULL, startdatetime timestamp without time zone NOT NULL, times_id bigint, CONSTRAINT timeson_pkey PRIMARY KEY (id), CONSTRAINT fkb1af5ba5890cf3da FOREIGN KEY (times_id) REFERENCES times (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) WITH (OIDS=FALSE); ALTER TABLE timeson OWNER TO myfreo; == I populate the table with some data and use pgAdmin III 1.8.4 to view the date "View date> first top100 rows". the question is: in what timezone are the fields showed in pgAdmin? no timezone (as stored), the server time zone or the time zone of the computer where pgAdmin runs? Thanks Fred
Re: [GENERAL] How to remove non-UTF values from a table?
Actually the title of my email should have been "how to **replace** utf-8 values". Thanks. On Mon, Dec 14, 2009 at 7:03 PM, Phoenix Kiula wrote: > An easy question for some I hope. > > I have a DB from 8.2 days that when I now dump and try to take into > the 8.3.7, it gives me errors about utf-8 stuff. > > I tried searching this list's archives but could not come up with an answer. > > Google returns some sites like these: > http://sniptools.com/databases/finding-non-utf8-values-in-postgresql - > but I'm not clear on how to use them. > > Following the SQL on this site I could identify some columns that > contain text like this: > > "Évolution générale de la situation démographique" > > So my guess is that the non-English characters were originally not > getting written in proper utf-8 variants. > > Is there any SQL possibility to find these columns and replace them > with utf-8 equivalents using some postgresql commands? Couldn't find > anything in the "Strings functions" (chapter 9 of manual). > > We're on CentOS. > > Thanks! > -- 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] How to remove non-UTF values from a table?
Actually I just realized that the SQL below will also pick up on these perfectly valid-looking columns: http://factfinder.census.gov/servlet/ReferenceMapFramesetServlet?_bm=y&-zip=27340&-PANEL_ID=rm_result&-_MapEvent=zoomToAddress&-street=&-city=&-rm_config=|b=50|l=en|t=420|zf=0.0|ms=ref_legal_00dec|dw=0.21626605473484609|dh=0.13180874155445527|dt=gov.census.aff.domain.map.EnglishMapExtent|if=gif|cx=-79.8023|cy=35.827|zl=5|pz=5|bo=404:315:314:313:323:321:319|bl=362:360:393:392:355:354:385|ft=350:349:335:389:388:332:331|fl=381:403:204:380:369:379:368|g=16000US3752760&-tree_id=420&-errMsg=&-redoLog=false&-geo_id=16000US3752760&-states= Which part of this is non-UTF8? Why is this going into a UTF8 table with corrupted values? The lc_collate etc and all settings I can imagine are already utf-8! Thanks for any pointers. On Mon, Dec 14, 2009 at 7:04 PM, Phoenix Kiula wrote: > Actually the title of my email should have been "how to **replace** > utf-8 values". > > Thanks. > > > > On Mon, Dec 14, 2009 at 7:03 PM, Phoenix Kiula > wrote: >> An easy question for some I hope. >> >> I have a DB from 8.2 days that when I now dump and try to take into >> the 8.3.7, it gives me errors about utf-8 stuff. >> >> I tried searching this list's archives but could not come up with an answer. >> >> Google returns some sites like these: >> http://sniptools.com/databases/finding-non-utf8-values-in-postgresql - >> but I'm not clear on how to use them. >> >> Following the SQL on this site I could identify some columns that >> contain text like this: >> >> "Évolution générale de la situation démographique" >> >> So my guess is that the non-English characters were originally not >> getting written in proper utf-8 variants. >> >> Is there any SQL possibility to find these columns and replace them >> with utf-8 equivalents using some postgresql commands? Couldn't find >> anything in the "Strings functions" (chapter 9 of manual). >> >> We're on CentOS. >> >> Thanks! >> > -- 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] Visibility of temporary database objects
On 14/12/2009 4:35 PM, Allan Kamau wrote: > and I am calling this function in from a threaded application which could use the same connection in more than one thread(is this possible/or even safe). No! Admittedly it depends on the client interface/driver, but at least for direct psql, PgODBC and PgJDBC it is _not_ safe to use a single PostgreSQL connection from more than one thread. Use multiple connections, or properly control access to the connection so that only one thread will be doing work on it at a time. Even then, you can't have multiple queries in flight on a single connection. -- Craig Ringer -- 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] getaddrinfo.c error
Tom Lane wrote: > Greg Smith writes: > > wrote: > >> Hi,guys.I ama studentwho want to studyPostgreSQL.I encountered the > >> following errors which I don't know how to fix while I using eclipse > >> to built pgsql: > > > I don't know why this specific one isn't working, but we do have a very > > detailed guide to getting PostgreSQL to build in Eclipse that might help > > you out: http://wiki.postgresql.org/wiki/Working_with_Eclipse > > Hm, I wonder whether the reason the OP ran into trouble was that he > followed that guide :-(. Relying on manual invocation of configure > is a sure recipe for hitting weird breakage anytime somebody changes > the configure input files ... Surely there's a way to teach Eclipse > to do that when needed? Is this really a problem? I think our makefiles are set up such that they will re-run configure in some of its input files have changed. I know they do for me when I cvs-update and configure itself has changed. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] getaddrinfo.c error
Alvaro Herrera writes: > Tom Lane wrote: >> Hm, I wonder whether the reason the OP ran into trouble was that he >> followed that guide :-(. Relying on manual invocation of configure >> is a sure recipe for hitting weird breakage anytime somebody changes >> the configure input files ... Surely there's a way to teach Eclipse >> to do that when needed? > Is this really a problem? I think our makefiles are set up such that > they will re-run configure in some of its input files have changed. I believe they will re-run config.status to regenerate the output files. This is not the same thing as re-running the configure script. regards, tom lane -- 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] How to remove non-UTF values from a table?
On 14 Dec 2009, at 13:21, Phoenix Kiula wrote: > Actually I just realized that the SQL below will also pick up on these > perfectly valid-looking columns: > > > http://factfinder.census.gov/servlet/ReferenceMapFramesetServlet?_bm=y&-zip=27340&-PANEL_ID=rm_result&-_MapEvent=zoomToAddress&-street=&-city=&-rm_config=|b=50|l=en|t=420|zf=0.0|ms=ref_legal_00dec|dw=0.21626605473484609|dh=0.13180874155445527|dt=gov.census.aff.domain.map.EnglishMapExtent|if=gif|cx=-79.8023|cy=35.827|zl=5|pz=5|bo=404:315:314:313:323:321:319|bl=362:360:393:392:355:354:385|ft=350:349:335:389:388:332:331|fl=381:403:204:380:369:379:368|g=16000US3752760&-tree_id=420&-errMsg=&-redoLog=false&-geo_id=16000US3752760&-states= I think you accidentally posted your holiday plans? > Which part of this is non-UTF8? Why is this going into a UTF8 table > with corrupted values? The lc_collate etc and all settings I can > imagine are already utf-8! > > Thanks for any pointers. Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4b264f49228051716694515! -- 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] pgAdmin III: timestamp displayed in what time zone?
On Monday 14 December 2009 3:04:07 am Fred Janon wrote: > Hi, > > I am using Postgres 8.3. I have a table defined like this: > > === > -- Table: timeson > > -- DROP TABLE timeson; > > CREATE TABLE timeson > ( > id bigint NOT NULL, > enddatetime timestamp without time zone NOT NULL, > startdatetime timestamp without time zone NOT NULL, > times_id bigint, > CONSTRAINT timeson_pkey PRIMARY KEY (id), > CONSTRAINT fkb1af5ba5890cf3da FOREIGN KEY (times_id) > REFERENCES times (id) MATCH SIMPLE > ON UPDATE NO ACTION ON DELETE NO ACTION > ) > WITH (OIDS=FALSE); > ALTER TABLE timeson OWNER TO myfreo; > > == > I populate the table with some data and use pgAdmin III 1.8.4 to view the > date "View date> first top100 rows". the question is: in what timezone are > the fields showed in pgAdmin? no timezone (as stored), the server time zone > or the time zone of the computer where pgAdmin runs? > > Thanks > > Fred For display purposes it has no time zone value, so it is just a literal value. For calculation purposes per the docs: http://www.postgresql.org/docs/8.4/interactive/datatype-datetime.html "Conversions between timestamp without time zone and timestamp with time zone normally assume that the timestamp without time zone value should be taken or given as timezone local time. A different time zone can be specified for the conversion using AT TIME ZONE. " -- Adrian Klaver akla...@comcast.net -- 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] Dependency tracking tool
"Philippe Lang" writes: > In order to have a "global map" of the dependencies of the functions, > views, tables in a PG database, I'd like to have some sort of > "dependency tracking tool", that would show for each object: > - which other objects depend on this object > - on which other objects this object depends You can find the raw data for that in pg_depend; at least for the sorts of dependencies that PG cares about, which might not be exactly what you are looking for. In particular we do not try to track what objects the code inside a function might refer to. > Is it correct to say that this "hierarchy" does not exist inside > Postgresql, and that it is necessary to parse the INFORMATION_SCHEMA.* > tables to get this information? The information_schema views do not expose that information at all. regards, tom lane -- 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] Dependency tracking tool
Philippe Lang wrote: > Hello, > > In order to have a "global map" of the dependencies of the functions, > views, tables in a PG database, I'd like to have some sort of > "dependency tracking tool", that would show for each object: > > - which other objects depend on this object > - on which other objects this object depends Greg Stark published an example query here http://www.pgcon.org/2009/schedule/events/181.en.html -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] Dependency tracking tool
Tom wrote: >> In order to have a "global map" of the dependencies of the functions, >> views, tables in a PG database, I'd like to have some sort of >> "dependency tracking tool"... >> >> >> >> Is it correct to say that this "hierarchy" does not exist inside >> Postgresql, and that it is necessary to parse the >> INFORMATION_SCHEMA.* tables to get this information? > > The information_schema views do not expose that information at all. The information schema exposes at least the functions definitions, through "INFORMATION_SCHEMA.routines.routine_definition". My idea was to parse the functions definitions in order to build dependencies between the functions. I'm not sure how difficult it is, especially with overloaded functions, which require more than a simple pattern search inside the function definition in order to be distinguished... Best regards, --- Philippe Lang Web: www.attiksystem.ch Attik SystemEmail : philippe.l...@attiksystem.ch rte de la Fonderie 2Phone : +41 26 422 13 75 1700 Fribourg Mobile : +41 79 351 49 94 Switzerland Fax: +41 26 422 13 76 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 8.4.2 - Windows Binaries
I might be bringing this up too soon, however the link to the 8.4.2 windows binaries needs to be updated: http://www.enterprisedb.com/products/pgdownload.do#windows It currently shows the binaries for 8.4.1. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PG Windows - Installer Error
When upgrading pg 8.4.1 to 8.4.2 the following error message appeared: -Error- [quote]"Error copying file from package archive (...)/postgresql-8.4.2-1-windows.exe to (...)/bin/comerr32.dll[/quote] After selecting retry, the install process completes successfully. P.S. I tried posting this to the enterprisedb forums but I get the following error message when submitting the post: An error has occurred. For detailed error information, please see the HTML source code, and contact the forum Administrator. java.lang.ClassCastException -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] postgres on Solaris 10 with ZFS
Hi All, I have solaris 10 update5 sunfire X4240 with 32GB RAM and ZFS system.I am having problem while configuring below parameters especially how shmmni works --- 1) shmmax 2) shmmni 3) arc_max 4) shared_buffers Since, it's a dedicated server i want to utilize all resources.As of now i have set parameters as below -- 1) shmmax -- 16GB ( size of single shared memory segment) 2) shmmni -- Default 100 3) arc_max -- 26 GB 4) shared_buffers -- 2 GB 5) effective_cache_size -- 5GB Also, would it be possible if i can have more memory segment for same postgesql.If yes How ? Please help. Regards, AG
Re: [GENERAL] 8.4.2 - Windows Binaries
On Mon, Dec 14, 2009 at 4:28 PM, Richard Broersma wrote: > I might be bringing this up too soon, however the link to the 8.4.2 > windows binaries needs to be updated: > > http://www.enterprisedb.com/products/pgdownload.do#windows > > It currently shows the binaries for 8.4.1. Did you try a reload? -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- 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] 8.4.2 - Windows Binaries
On Mon, Dec 14, 2009 at 9:10 AM, Dave Page wrote: >> It currently shows the binaries for 8.4.1. > Did you try a reload? Yes, It shows 8.4.2. Actually the page refreshed two minutes after I sent the email. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- 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] How to remove non-UTF values from a table?
Phoenix Kiula writes: > Is there any SQL possibility to find these columns and replace them > with utf-8 equivalents using some postgresql commands? Couldn't find > anything in the "Strings functions" (chapter 9 of manual). I've bookmarked this for later: http://archives.postgresql.org/pgsql-general/2009-07/msg00904.php Regards, -- dim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL Conference East call for papers
December 14th, 2009, the PostgreSQL Conference U.S. team is pleased to announce the East 2010 venue and call for papers. This year the premiere East Coast PostgreSQL Conference will be returning to history Drexel University in Philadelphia. The event this year is being held at Drexel University in Philadelphia from March 26th through 28th. Following previously successful United States PostgreSQL conferences, we will be hosting a series of 3-4 hour tutorials, 90 minute mini-tutorials, 45 minute talks, 5 minute lightning talks and a new 30 minute presentation time slot. Time line: December 14th: Talk submission opens January 30th: Talk submission closes February 15th: Speaker notification This year we will be continuing our trend of covering the entire PostgreSQL ecosystem. We would like to see talks and tutorials on the following topics: * General PostgreSQL: * Administration * Performance * High Availability * Migration * GIS * Integration * Solutions and White Papers * The Stack: * Python/Django/Pylons/TurboGears/Custom * Perl5/Catalyst/Bricolage * Ruby/Rails * Java (PLJava would be great)/Groovy/Grails * Operating System optimization (Linux/FBSD/Solaris/Windows) * Solutions and White Papers Submit Talk: http://www.postgresqlconference.org/ Sincerely, Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering If the world pushes look it in the eye and GRR. Then push back harder. - Salamander -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 8.3 PL/pgSQL comparing arbitrary records
Hi all,
Short version of my question:
What's the best way to compare arbitrary records (OLD and NEW, in my
case) using PL/pgSQL in Postgres 8.3, without knowing anything about
the structure of the records? If the answer is to cast OLD and NEW to
text, and then compare, as suggested in [1], what false equalities
could be produced, assuming the records have identical structure and
NULLs are allowed?
Long version:
I am porting a trigger function written in PL/Python to PL/pgSQL. The
function, called track_updates() is declared as a BEFORE INSERT OR
UPDATE trigger on many tables in our database; simplifying a bit, its
job is to bump up an "updated" timestamp whenever a row has been
modified.
This simple plpython snippet tests whether the old and new rows are
identical (i.e. an update with no effect), and ignores if so:
if TD["new"] == TD["old"]:
return "SKIP"
I'd like to perform a similar test in plpgsql, but the straightforward:
IF NEW IS NOT DISTINCT FROM OLD THEN
RETURN NEW;
doesn't work -- I get:
ERROR: operator does not exist: [my table name] = [my table name]
LINE 1: SELECT $1 IS NOT DISTINCT FROM $2
HINT: No operator matches the given name and argument type(s).
You might need to add explicit type casts.
According to discussion[1] ("8.3 PLpgSQL Can't Compare Records?") a
few months ago, the workaround for < 8.4 is to cast OLD and NEW to
text, and then compare the two text values. A comment there suggests
that comparing NULL and the empty string in this way might incorrectly
result in a true equality test, which is a little worrying for my
purposes. However, I'm unable to reproduce NULL and '' equating to
each other when cast to text (see example code below). Are there any
false equalities or other gotchas I should be worried about when
comparing OLD::text and NEW::text? I can safely assume for my purposes
that the old and new records will have the same structure (i.e. no
ALTER TABLEs to worry about).
I'm using Postgres 8.3.4, compiled from source on Linux.
Thanks,
Josh
CREATE TEMPORARY TABLE test_trg (
colA int,
colB text,
colC text,
updated timestamp with time zone NOT NULL
) ON COMMIT DROP;
CREATE OR REPLACE FUNCTION pg_temp.track_updates()
RETURNS TRIGGER AS $$
DECLARE
BEGIN
IF TG_OP = 'UPDATE' THEN
IF NEW::text IS NOT DISTINCT FROM OLD::text THEN
RAISE NOTICE 'OLD and NEW are the same!';
ELSE
RAISE NOTICE 'OLD and NEW are different!';
END IF;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER "track_updates_trg"
BEFORE INSERT OR UPDATE ON "pg_temp"."test_trg"
FOR EACH ROW EXECUTE PROCEDURE pg_temp.track_updates();
INSERT INTO pg_temp.test_trg (colA, colB, colC, updated)
VALUES (1, '', NULL, CURRENT_TIMESTAMP);
-- Each of these UPDATEs say 'OLD and NEW are different!'
UPDATE test_trg SET colB = NULL;
UPDATE test_trg SET colC = '';
UPDATE test_trg SET colC = NULL;
UPDATE test_trg SET colB = '', colC = '';
--
Footnotes:
[1] http://archives.postgresql.org/pgsql-hackers/2009-07/msg00040.php
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Clarify postgresql.conf statement_timeout
I have a few things to report so I'm not sure if one email is good or several but here goes. We are using Postgresql 8.3.8 We were having a blocking query problem that should have been fixed by statement_timeout = 9 however this seems to have had zero effect. The query we have was like so: update articles set views=views+1 where id=7223 Thats it. Fairly simple right? Well, we also had a trigger function that updates a full text index on that record whenever any value is updated. We have since changed this function to only update the gist index for inserts or updates when one of the indexed columns is updated. However, let's stick with the original for a moment. There should have really been no problem updating the GIST index for a single row in an insert/update trigger. So what happened is, the above update never completed and the Postgresql service consumed all available memory. We had to forcefully reboot the machine, we turned on track activity, and watch it do it again and again. Luckily we were able to kill the process with the offending query before losing the machine. The postgresql configuration has a max of 255 connections. The machine has 16 gigabytes of RAM and 2 quad core xeons. We have several instances of Postgresql running on different ports. Our reason for doing this was to prevent one customer's database 'instance' from impacting another customer. A couple of years ago we had a run away query that brought the whole system down. So I implemented this separate instance concept and it has been purring along great ever since, until now. So we contacted a PG expert who was able to determine we had a corrupt full text index and recommended rebuilding it and fixing the trigger function. Once we rebuilt the index things worked (or are working) so far. So we have a couple of questions: Why is it that statement_timeout was ignored and the update statement was allowed to run for excessive time? Why does Postgresql NOT have a maximum memory allowed setting? We want to allocate resources efficiently and cannot allow one customer to impact others. That's it for now. Hope someone can provide helpful answers. Thanks, Mark W.
[GENERAL] Announcing Gluster Storage Platform
Greetings! The Gluster Team is happy to announce the release of Gluster Storage Platform 3.0. The Gluster Storage Platform is based on the popular open source clustered file system GlusterFS, integrating the file system, an operating system layer, a web based management interface, and an easy to use installer. Gluster Storage Platform is an open source clustered storage solution. The software is a powerful and flexible solution that simplifies the task of managing unstructured file data whether you have a few terabytes of storage or multiple petabytes. Gluster Storage Platform runs on industry standard hardware from any vendor and delivers multiple times the scalability and performance of conventional storage at a fraction of the cost. To learn more please check us out at www.gluster.org where you can download source and binary, read release notes, and engage with the community. If you are already using Gluster, please help strengthen our community by leaving your mark on Who is using Gluster page: http://www.gluster.com/community/whoisusing.php Happy Hacking -- Gluster Team -- 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] 8.3 PL/pgSQL comparing arbitrary records
Hello
NEW and OLD is comparable in 8.4. In 8.3 and older you have to use
little bit different syntax
http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_compare_variables_NEW_and_OLD_in_trigger.27s_body
Regards
Pavel Stehule
2009/12/15 Josh Kupershmidt :
> Hi all,
>
> Short version of my question:
> What's the best way to compare arbitrary records (OLD and NEW, in my
> case) using PL/pgSQL in Postgres 8.3, without knowing anything about
> the structure of the records? If the answer is to cast OLD and NEW to
> text, and then compare, as suggested in [1], what false equalities
> could be produced, assuming the records have identical structure and
> NULLs are allowed?
>
> Long version:
> I am porting a trigger function written in PL/Python to PL/pgSQL. The
> function, called track_updates() is declared as a BEFORE INSERT OR
> UPDATE trigger on many tables in our database; simplifying a bit, its
> job is to bump up an "updated" timestamp whenever a row has been
> modified.
>
> This simple plpython snippet tests whether the old and new rows are
> identical (i.e. an update with no effect), and ignores if so:
>
> if TD["new"] == TD["old"]:
> return "SKIP"
>
> I'd like to perform a similar test in plpgsql, but the straightforward:
>
> IF NEW IS NOT DISTINCT FROM OLD THEN
> RETURN NEW;
>
> doesn't work -- I get:
>
> ERROR: operator does not exist: [my table name] = [my table name]
> LINE 1: SELECT $1 IS NOT DISTINCT FROM $2
> HINT: No operator matches the given name and argument type(s).
> You might need to add explicit type casts.
>
> According to discussion[1] ("8.3 PLpgSQL Can't Compare Records?") a
> few months ago, the workaround for < 8.4 is to cast OLD and NEW to
> text, and then compare the two text values. A comment there suggests
> that comparing NULL and the empty string in this way might incorrectly
> result in a true equality test, which is a little worrying for my
> purposes. However, I'm unable to reproduce NULL and '' equating to
> each other when cast to text (see example code below). Are there any
> false equalities or other gotchas I should be worried about when
> comparing OLD::text and NEW::text? I can safely assume for my purposes
> that the old and new records will have the same structure (i.e. no
> ALTER TABLEs to worry about).
>
> I'm using Postgres 8.3.4, compiled from source on Linux.
>
> Thanks,
> Josh
>
>
> CREATE TEMPORARY TABLE test_trg (
> colA int,
> colB text,
> colC text,
> updated timestamp with time zone NOT NULL
> ) ON COMMIT DROP;
>
> CREATE OR REPLACE FUNCTION pg_temp.track_updates()
> RETURNS TRIGGER AS $$
> DECLARE
> BEGIN
> IF TG_OP = 'UPDATE' THEN
> IF NEW::text IS NOT DISTINCT FROM OLD::text THEN
> RAISE NOTICE 'OLD and NEW are the same!';
> ELSE
> RAISE NOTICE 'OLD and NEW are different!';
> END IF;
> END IF;
> RETURN NEW;
> END;
> $$ LANGUAGE plpgsql;
>
> CREATE TRIGGER "track_updates_trg"
> BEFORE INSERT OR UPDATE ON "pg_temp"."test_trg"
> FOR EACH ROW EXECUTE PROCEDURE pg_temp.track_updates();
>
> INSERT INTO pg_temp.test_trg (colA, colB, colC, updated)
> VALUES (1, '', NULL, CURRENT_TIMESTAMP);
>
> -- Each of these UPDATEs say 'OLD and NEW are different!'
> UPDATE test_trg SET colB = NULL;
> UPDATE test_trg SET colC = '';
> UPDATE test_trg SET colC = NULL;
> UPDATE test_trg SET colB = '', colC = '';
>
> --
> Footnotes:
> [1] http://archives.postgresql.org/pgsql-hackers/2009-07/msg00040.php
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
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] 8.3 PL/pgSQL comparing arbitrary records
Hello.
It seems that this works (I made tests on permanent table, postgresql
8.4.0):
CREATE OR REPLACE FUNCTION track_updates()
RETURNS TRIGGER AS $$
DECLARE
BEGIN
IF TG_OP = 'UPDATE' THEN
IF NEW IS NOT DISTINCT FROM OLD THEN
RAISE NOTICE 'OLD and NEW are the same!';
ELSE
RAISE NOTICE 'OLD and NEW are different!';
END IF;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
> update test_trg set cola=1 where cola=1;
NOTICE: OLD and NEW are the same!
> update test_trg set cola=2 where cola=1;
NOTICE: OLD and NEW are different!
Josh Kupershmidt wrote:
Hi all,
Short version of my question:
What's the best way to compare arbitrary records (OLD and NEW, in my
case) using PL/pgSQL in Postgres 8.3, without knowing anything about
the structure of the records? If the answer is to cast OLD and NEW to
text, and then compare, as suggested in [1], what false equalities
could be produced, assuming the records have identical structure and
NULLs are allowed?
Long version:
I am porting a trigger function written in PL/Python to PL/pgSQL. The
function, called track_updates() is declared as a BEFORE INSERT OR
UPDATE trigger on many tables in our database; simplifying a bit, its
job is to bump up an "updated" timestamp whenever a row has been
modified.
This simple plpython snippet tests whether the old and new rows are
identical (i.e. an update with no effect), and ignores if so:
if TD["new"] == TD["old"]:
return "SKIP"
I'd like to perform a similar test in plpgsql, but the straightforward:
IF NEW IS NOT DISTINCT FROM OLD THEN
RETURN NEW;
doesn't work -- I get:
ERROR: operator does not exist: [my table name] = [my table name]
LINE 1: SELECT $1 IS NOT DISTINCT FROM $2
HINT: No operator matches the given name and argument type(s).
You might need to add explicit type casts.
According to discussion[1] ("8.3 PLpgSQL Can't Compare Records?") a
few months ago, the workaround for < 8.4 is to cast OLD and NEW to
text, and then compare the two text values. A comment there suggests
that comparing NULL and the empty string in this way might incorrectly
result in a true equality test, which is a little worrying for my
purposes. However, I'm unable to reproduce NULL and '' equating to
each other when cast to text (see example code below). Are there any
false equalities or other gotchas I should be worried about when
comparing OLD::text and NEW::text? I can safely assume for my purposes
that the old and new records will have the same structure (i.e. no
ALTER TABLEs to worry about).
I'm using Postgres 8.3.4, compiled from source on Linux.
Thanks,
Josh
CREATE TEMPORARY TABLE test_trg (
colA int,
colB text,
colC text,
updated timestamp with time zone NOT NULL
) ON COMMIT DROP;
CREATE OR REPLACE FUNCTION pg_temp.track_updates()
RETURNS TRIGGER AS $$
DECLARE
BEGIN
IF TG_OP = 'UPDATE' THEN
IF NEW::text IS NOT DISTINCT FROM OLD::text THEN
RAISE NOTICE 'OLD and NEW are the same!';
ELSE
RAISE NOTICE 'OLD and NEW are different!';
END IF;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER "track_updates_trg"
BEFORE INSERT OR UPDATE ON "pg_temp"."test_trg"
FOR EACH ROW EXECUTE PROCEDURE pg_temp.track_updates();
INSERT INTO pg_temp.test_trg (colA, colB, colC, updated)
VALUES (1, '', NULL, CURRENT_TIMESTAMP);
-- Each of these UPDATEs say 'OLD and NEW are different!'
UPDATE test_trg SET colB = NULL;
UPDATE test_trg SET colC = '';
UPDATE test_trg SET colC = NULL;
UPDATE test_trg SET colB = '', colC = '';
--
Footnotes:
[1] http://archives.postgresql.org/pgsql-hackers/2009-07/msg00040.php
--
С уважением,
Александр Пыхалов,
системный администратор ЮГИНФО ЮФУ.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
