[GENERAL] Problem on installing postgresql-devel
Hi folks, CentOS 4.4 x86_84 On installing postgresql-devel; $ sudo yum install postgresql-devel Setting up Install Process Setting up repositories Reading repository metadata in from local files Parsing package install arguments Resolving Dependencies --> Populating transaction set with selected packages. Please wait. ---> Package postgresql-devel.x86_64 0:7.4.16-1.RHEL4.1 set to be updated --> Running transaction check --> Processing Dependency: postgresql = 7.4.16-1.RHEL4.1 for package: postgresql-devel --> Finished Dependency Resolution Error: Missing Dependency: postgresql = 7.4.16-1.RHEL4.1 is needed by package postgresql-devel * end * It needed "postgresql version 7.4.16-1.RHEL4.1" On running; $ sudo yum search postgresql | grep postgresql.x86_64 postgresql.x86_647.4.13-2.RHEL4.1 base freeradius-postgresql.x86_64 1.0.1-3.RHEL4.3base rekall-postgresql.x86_64 2.2.4-8.el4.kb kbs-CentOS-Extra snort-postgresql.x86_64 2.4.3-1.el4.kb kbs-CentOS-Extra rekall-postgresql.x86_64 2.2.3-5.el4.kb kbs-CentOS-Extra rekall-postgresql.x86_64 2.2.4-1.el4.kb kbs-CentOS-Extra rekall-postgresql.x86_64 2.2.4-2.el4.kb kbs-CentOS-Extra rekall-postgresql.x86_64 2.2.4-3.el4.kb kbs-CentOS-Extra rekall-postgresql.x86_64 2.2.4-4.el4.kb kbs-CentOS-Extra rekall-postgresql.x86_64 2.4.0-4.el4.kb kbs-CentOS-Extra ser-postgresql.x86_640.9.6-6.el4.kb kbs-CentOS-Extra snort-postgresql.x86_64 2.4.4-2.el4.kb kbs-CentOS-Extra snort-postgresql.x86_64 2.4.4-3.el4.kb kbs-CentOS-Extra postgresql.x86_647.4.16-1.RHEL4.1 update postgresql.x86_648.1.8-1.el4s1.1 installed * end * It found "postgresql.x86_64 version 8.1.8-1.el4s1.1" already installed. However I can't find "postgresql version 7.4.16-1.RHEL4.1" on the repo. Only "version 7.4.13-2.RHEL4.1" Please advise how to fix the problem. TIA B.R. Stephen Liu Send instant messages to your online friends http://uk.messenger.yahoo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] foreign key constraints with inhertiance, hack suggestions?
George Nychis wrote: > Basically I have a master 'flows' table which is partitioned and has > non-overlapping CHECK constraints on each partition. Each record in the > partitions have a unique pair of attributes: interval, flow_id > > When inserting in to another table 'flow_labels', these two attributes > are given, I want to ensure that there exists a single flow in some > partition in the 'flows' table that has these two values. > > I'm no DB expert, but could there be some sort of rule or trigger on > insert into 'flow_labels' that does a count() where these two values are > exact to ensure there exists 1 flow? Problem is my 'flows' table is on > the order of billions of flows, each partition having hundreds of > thousands. I think count() would be a last resort solution. If possible, I think it would help to add the interval data to your 'flow_labels' table and join on the combined key. That way the information that divides your 'flows' into different tables is available at join time, and constraint exclusion could do its work. That'd still require some way to inherit constraints, but it's a start. -- 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 2: Don't 'kill -9' the postmaster
Re: [GENERAL] questions about query design
Ottavio Campana wrote: > Here's an example of what I'm doing: I have a table like > > create table ( > id serial, > description text not null, > active boolean default true); > > What I want to do is a function inserting a new item into the table > ensuring that there is only one record in the table having a particular > description and at the same time the active field set to true (it might > seem stupid, but the application requires it). Assuming active can't be NULL, what's wrong with a UNIQUE constraint on (description, active)? -- 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 6: explain analyze is your friend
Re: [GENERAL] Problem on installing postgresql-devel
Hi, On Thu, 2007-03-22 at 15:56 +0800, Stephen Liu wrote: > Please advise how to fix the problem. You should either complain to CentOS guys, or use PGDG RPMs... Regards, -- Devrim GÜNDÜZ PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/ signature.asc Description: This is a digitally signed message part
[GENERAL] Dealing with table names in functions
Is there a safe way to deal with tables being passed into a function, specifically in terms of what schema they're in? I can just blindly accept a text string and hope that it's always evaluated in the correct search_path context, but that doesn't seem so good. OTOH, if I accept an OID, there's no great way to pass that to most of the rest of the system... I can cast the OID to regclass, but that doesn't get me a fully-qualified name. It would be nice if there was a way to convert an OID into a fully- qualified name. I'm working on some partitioning stuff, and I'm currently writing a function that will return the name of a partition given the parent table and what period to partition on (ie: day, month, year, etc). Originally, I thought I'd just accept an OID for the table name, but I can't think of a safe way to look up that tables schema name (because I want to return a fully qualified name). Obviously, I can look in pg_class and pg_namespace, but someone could do a DROP TABLE between when I do that lookup and when I actually use the name. So I thought I'd just do a LOCK TABLE... except I need the table name to do that. Catch-22. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Configuring phpPgAdmin and pg_ctl reload
Ashish Karalkar wrote: Try pg_ctl -D /path to pg data home e.g. pg_ctl -D /usr/local/pgsql/data Hope this will help - Original Message - From: "John Meyer" <[EMAIL PROTECTED]> To: "postgresql-general" Sent: Thursday, March 22, 2007 8:31 AM Subject: [GENERAL] Configuring phpPgAdmin and pg_ctl reload I'm setting up phpPgAdmin and I finally get to the point where you reconfigure pg_hba.conf One of the lines says to reload the values, type the command pg_ctl reload. I try it as super user, no go, I su into postgres, it complains: pg_ctl reload pg_ctl: no database directory specified and environment variable PGDATA unset Try "pg_ctl --help" for more information. Okay. Maybe it was just understood, but I was typing in word for word the entry from pg_hba.conf, but I'll keep that for reference later on. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Postgres Hot Standby. How or when does the recovery db move recovery.conf to recovery.done?
On 3/21/07, Dhaval Shah <[EMAIL PROTECTED]> wrote: From one of Tom's reply to a different poster, I found that one can run pg_resetxlog. http://www.postgresql.org/docs/8.2/static/app-pgresetxlog.html, to make the db recover and startup. Appears not for the faint hearted! Dhaval On 3/21/07, Dhaval Shah <[EMAIL PROTECTED]> wrote: > I looked at the pg_standby utility and would have liked to use it, > however there are some customer driven extraneous issues in using > that. > > What I am looking at it is this: > > 1. I can detect that the primary has gone down and return a non-zero > for the standby to recover. > > 2. Since I can detect that I am out of standby mode, I can shutdown > the postgres, move the recovery.conf file to recovery.done manually. > And then restart the db. > > Even if I do step 2, I still get the following in the server log: > > = > Main: Triggering Recovery!!! <- my script is returning a non-zero code here ... > > PANIC: could not open file "pg_xlog/0001001B" (log > file 0, segment 27): No such file or directory If you are getting these errors there is something wrong with your log shipping method. You are missing WAL files that are needed to bring the server back into recovery...pg_resetxlog will not help you re-recover the server although it may allow you to bring the server up with some (possibly a lot) of data loss. This is coming from the fact that for a 'hot standby', you need to take extra precautions to preserve old WAL files. AIUI, the server needs to go far enough back in 'WAL time' to see the last checkpoint, which is not available. Even if you can't use it, get a copy of the pg_standby utility and get a really good understanding of how it works. It has a clever 'symlink' mode which neatly bypasses the complexity of maintaining a standby system. It is one C file and is well documented. merlin ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Dealing with table names in functions
On 3/22/07, Jim Nasby <[EMAIL PROTECTED]> wrote: Is there a safe way to deal with tables being passed into a function, specifically in terms of what schema they're in? I can just blindly accept a text string and hope that it's always evaluated in the correct search_path context, but that doesn't seem so good. OTOH, if I accept an OID, there's no great way to pass that to most of the rest of the system... I can cast the OID to regclass, but that doesn't get me a fully-qualified name. It would be nice if there was a way to convert an OID into a fully- qualified name. I'm working on some partitioning stuff, and I'm currently writing a function that will return the name of a partition given the parent table and what period to partition on (ie: day, month, year, etc). Originally, I thought I'd just accept an OID for the table name, but I can't think of a safe way to look up that tables schema name (because I want to return a fully qualified name). Obviously, I can look in pg_class and pg_namespace, but someone could do a DROP TABLE between when I do that lookup and when I actually use the name. So I thought I'd just do a LOCK TABLE... except I need the table name to do that. Catch-22. You can select pg_class name in transaction by oid 'for update'. This will block drop table, etc. from other sessions. This will only work if you are the superuser however. merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Dealing with table names in functions
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
> Is there a safe way to deal with tables being passed into a function,
> specifically in terms of what schema they're in?
Pass in the schema and tablename together as a string:
select foobar('public.baz');
or (better, IMO) make it two separate arguments:
select foobar('baz', 'public');
I usually put the table first as it allows me to overload the function
with a single arg and a default schema.
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200703220923
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iD8DBQFGAoOGvJuQZxSWSsgRAxYOAJ9kuyz8YY+LvMsVxHSuqFbintcSAQCfWX6y
zk5PVMhN9Pqxxkwvy/erCbw=
=ZTzZ
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[GENERAL] xpath_list() function
Hi, I have installed xml2 contrib to my postgreSQL 8.1.4 install. The functionality it offer when working with xml files is really good, but i currently experiencing a few issues with the xpath_list function and a simple test. I took a simple xml file (the one described in the PostgreSQL book by Korry and Susan Douglas), just for testing purposes added an extra film with 2 year tags containing the values 1972 and 1973 . I can extract this information with the xpath_list function which returns the following testdb=# SELECT film_name, xpath_list(description, 'year') FROM filminfo; film_name | xpath_list --- + Casablanca | 1942 Rear Window | 1954 The Godfather | 1972 Test film | 1973,1972 It would seem reasonable in this example that the 1973,1972 gained from xpath_list could be reused in a WHERE clause like so SELECT film_name WHERE '1973' IN (xpath_list(description, 'year')); But the xpath_list function returns a single string so the above query returns no rows. I can not find a way of splitting the values returned from xpath_list so that they can be used in an IN () clause, can anybody point me in the right direction for a method that can split the result, or offer me some advice on how to achieve it. Thanks in advance, Andy
Re: [GENERAL] Dealing with table names in functions
Jim Nasby wrote: > Is there a safe way to deal with tables being passed into a function, > specifically in terms of what schema they're in? I can just blindly > accept a text string and hope that it's always evaluated in the > correct search_path context, but that doesn't seem so good. OTOH, if > I accept an OID, there's no great way to pass that to most of the > rest of the system... I can cast the OID to regclass, but that > doesn't get me a fully-qualified name. Pass the optionally qualified name and cast it to regclass. It will work correctly when the name is not qualified, applying search_path, and it will also work when the name is qualified. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(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] to_tsvector in 8.2.3
On Wed, Mar 21, 2007 at 09:13:55PM +0300, Teodor Sigaev wrote:
> >postgres=# select to_tsvector('test text');
> > to_tsvector
> >---
> > 'test text':1
> >(1 row)
> Ok. that's related to
> http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/tsearch2/wordparser/parser.c.diff?r1=1.11;r2=1.12;f=h
> commit. Thomas pointed that it can be non-breakable space (0xa0) and that
> commit assumes any character with C locale and multibyte encoding and >
> 0x7f is alpha.
> To check theory, pls, apply attached patch.
>
> If so, I'm confused, we can not assume that 0xa0 is a space symbol in any
> multibyte encoding, even in Windows.
Nope, same result with this patch.
//Magnus
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] using 'ALTER ROLE' in a function
Dear list, I'm trying to write a PL/pgSQL function that executes some code every time a user changes his/her password. The function is supposed to work like this: CREATE OR REPLACE FUNCTION changePwd(varchar(255)) RETURNS boolean AS $$ DECLARE pwd varchar(255); curr_user name; BEGIN curr_user = (SELECT session_user); pwd = $2; ALTER ROLE curr_user WITH ENCRYPTED PASSWORD pwd; -- [... do some other stuff ...] RETURN true; END; $$ LANGUAGE 'PLPGSQL'; but PostgreSQL returns: ERROR: Error »syntax error« at »$1« at character 13 QUERY: ALTER ROLE $1 WITH ENCRYPTED PASSWORD $2 I have tried this in several ways and it seems, ALTER ROLE just does not accept a parameter instead of name. Does anybody have a solution for this? Thanks in advance and best regards, Lutz ---(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
[GENERAL] Deadlock with REINDEX TABLE
Hi, I'm hoping some one can help me figure out how I ended up with a deadlock while running my reindex script last night. It basically partitions our table set and goes through each group of tables in parallel running REINDEX TABLE on each table in that group with each group having its own log. Anyways, I woke up this moring to find this in one of the logs: Mar 21 19:36:18 [info] User Info: REINDEX TABLE emma_messages_email_queue; [nativecode=ERROR: deadlock detected DETAIL: Process 12912 waits for AccessExclusiveLock on relation 138763808 of database 16384; blocked by process 15217. Process 15217 waits for RowExclusiveLock on relation 17111 of database 16384; blocked by process 12912.] Relation 138763808 is the primary key index on the table relation 17111. Proc 12912 is the REINDEX and proc 15217 is the contending query, I'm assuming it was an update due being a RowExclusive lock. How did this happen? Does REINDEX TABLE not take out the locks for each of the table's indexes at the same time (there was another index on the table but I'm not sure whether or not it was reindexed or not)? Do REINDEX TABLE and UPDATE not take out their locks in the same order (i.e. lock table, lock indexes)? erik jones <[EMAIL PROTECTED]> software developer 615-296-0838 emma(r)
Re: [GENERAL] Insert fail: could not open relation with OID 3221204992
[EMAIL PROTECTED] writes: > Here is the schema info: > ... > shape| st_geometry | > st_geometry is our own implementation for geometry type. To be blunt, I'd suggest looking there first. Can you duplicate the failure when loading into a table with no custom datatype? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] xpath_list() function
Original Message From Andy Dale Hi, testdb=# SELECT film_name, xpath_list(description, 'year') FROM filminfo; film_name | xpath_list --- + Casablanca | 1942 Rear Window | 1954 The Godfather | 1972 Test film | 1973,1972 It would seem reasonable in this example that the 1973,1972 gained from xpath_list could be reused in a WHERE clause like so SELECT film_name WHERE '1973' IN (xpath_list(description, 'year')); How about SELECT film_name WHERE (ARRAY['1973'] <@ STRING_TO_ARRAY((xpath_list(description, 'year')), ',' ); ? (not tested...) Regards, George ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] questions about query design
Alban Hertroys <[EMAIL PROTECTED]> writes: > Ottavio Campana wrote: >> What I want to do is a function inserting a new item into the table >> ensuring that there is only one record in the table having a particular >> description and at the same time the active field set to true (it might >> seem stupid, but the application requires it). > Assuming active can't be NULL, what's wrong with a UNIQUE constraint on > (description, active)? I think he does not want descriptions to be unique among non-active entries. If so, the right thing is a partial unique index: CREATE UNIQUE INDEX ... on (description) WHERE active; regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Dealing with table names in functions
Jim Nasby <[EMAIL PROTECTED]> writes: > ... I can cast the OID to regclass, but that > doesn't get me a fully-qualified name. It does if the name needs to be qualified given your current search_path. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Configuring phpPgAdmin and pg_ctl reload
John Meyer <[EMAIL PROTECTED]> writes: > Maybe it was just understood, but I was typing in word for word the > entry from pg_hba.conf, but I'll keep that for reference later on. pg_ctl needs either the data directory supplied on the command line, or PGDATA set in the environment, which reading the manpage would have told you. ;) -Doug ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Bug in CREATE/DROP TABLESPACE command
Postgres 8.2.3 on Windows Server 2003. I looked into this more, and I narrowed the bug down. It only happens if you issue a DROP TABLESPACE command and a CREATE TABLESPACE command in one batch, where the CREATE TABLESPACE command points to an invalid location. I didn't realize how obscure an edge-case this was. The code below will demonstrate the problem, with running commentary: /* To duplicate the tablespace bug on Postgres 8.2.3 on Windows 2003 Server: 1) Create a directory c:\postgresql\MyDatabase and set the postgresql user so it has full control of the directory.. Alternatively, change the path to some other path that you prefer 2) Run the first CREATE TABLESPACE command in it's own batch (I did this by highlighting it in pgadmin3) 3) Run the second two commands in one batch. That is, the drop and the create at once. 4) Run the drop tablespace command. Alternatively, you can delete it manually via pgadmin3. */ -- Create a tablespace in a valid location CREATE TABLESPACE bad_tablespace LOCATION E'C:\\postgresql\\MyDatabase'; -- Result: -- Query returned successfully with no result in 20 ms. -- Drop the tablespace and re-create in in an invalid location -- This only causes the bug if both these commands are run in one batch DROP TABLESPACE IF EXISTS bad_tablespace; CREATE TABLESPACE bad_tablespace LOCATION E'Z:\\postgresql\\MyDatabase'; -- Result: -- ERROR: could not set permissions on directory -- "Z:/postgresql/MyDatabase": No such file or directory -- SQL state: 58P01 -- Now try to drop it again, and you get an error DROP TABLESPACE IF EXISTS bad_tablespace; -- Result: -- ERROR: could not open directory "pg_tblspc/16827": No such file or -- directory -- SQL state: 58P01 It looks to me like postgres creates a hard link with a random number that points to the physical location of the tablespace. Once you get stuck like this, you can work around the problem by creating a C:\Program Files\PostgreSQL\8.2\data\pg_tblspc\# directory. I assume this problem is reproducible on other operating systems the same way. But maybe it is some problem specific to symbolic links on Windows? Looks more like an internal state issue though. Bruce Momjian wrote: What version of PostgreSQL is this? Please provide the SQL commands that cause this problem, with error output. --- William Garrison wrote: On Windows Server 2003, if you create a tablespace to a location that doesn't exist, then try to remove that tablespace, you get an error that pg_tblspc/# does not exist. It appears that postgres created the tablespace internally, but not the folder. When you try to drop the tablespace, the folder doesn't exist and it reports an error. This sounds like two interacting bugs: 1) The tablespace should not have been created because the symlink could not be created. 2) It should be possible to remove a tablespace even if the symlink has already been deleted manually. The workaround is to create a pg_tblsc/# directory then do the drop. Where do I submit this bug? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] using 'ALTER ROLE' in a function
Lutz Broedel <[EMAIL PROTECTED]> writes: > ERROR: Error »syntax error« at »$1« at character 13 > QUERY: ALTER ROLE $1 WITH ENCRYPTED PASSWORD $2 > > I have tried this in several ways and it seems, ALTER ROLE just does > not accept a parameter instead of name. Does anybody have a solution > for this? You probably need to build the query dynamically and use EXECUTE. -Doug ---(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] xpath_list() function
Hi, Unfortunately the suggestion by Geogre did not work, but i solved it like so: SELECT film_name FROM filminfo WHERE '1973' = ANY (STRING_TO_ARRAY((xpath_list(description, 'year')),',')) Thanks, Andy On 22/03/07, George Weaver <[EMAIL PROTECTED]> wrote: Original Message From Andy Dale >Hi, >testdb=# SELECT film_name, xpath_list(description, 'year') FROM filminfo; >film_name | xpath_list >--- + >Casablanca | 1942 >Rear Window | 1954 >The Godfather | 1972 >Test film | 1973,1972 >It would seem reasonable in this example that the 1973,1972 gained from >xpath_list could be reused in a WHERE clause like so >SELECT film_name WHERE '1973' IN (xpath_list(description, 'year')); How about SELECT film_name WHERE (ARRAY['1973'] <@ STRING_TO_ARRAY((xpath_list(description, 'year')), ',' ); ? (not tested...) Regards, George ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Server
I've been using the PostgreSQL server without really understanding how it works or how it is structured. Could someone point me to a 'simple' description??? Bob Pawley
Re: [GENERAL] to_tsvector in 8.2.3
Solved, see attached patch. I had found old Celeron-300 box and install Windows
on it, and it was very slow :)
Nope, same result with this patch.
Thank you.
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
*** ./contrib/tsearch2.orig/./wordparser/parser.c Thu Mar 22 18:39:23 2007
--- ./contrib/tsearch2/./wordparser/parser.cThu Mar 22 18:51:23 2007
***
*** 117,123
{
if (lc_ctype_is_c())
{
! unsigned int c = *(unsigned int*)(prs->wstr +
prs->state->poschar);
/*
* any non-ascii symbol with multibyte encoding
--- 117,123
{
if (lc_ctype_is_c())
{
! unsigned int c = *(prs->wstr + prs->state->poschar);
/*
* any non-ascii symbol with multibyte encoding
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Server
> I've been using the PostgreSQL server without really understanding how it > works or how it is > structured. > > Could someone point me to a 'simple' description??? http://www.postgresql.org/about/ Regards, Richard Broersma Jr. ---(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] Dealing with table names in functions
On Thu, 2007-03-22 at 09:40 -0400, Alvaro Herrera wrote: > Pass the optionally qualified name and cast it to regclass. It will > work correctly when the name is not qualified, applying search_path, > and it will also work when the name is qualified. Is there a way to get names that are always qualified, irrespective of the search_path? Once one has the oid, it's an easy function to write, but I suspect this machinery already exists. For example, I'd like the hypothetical cast: [EMAIL PROTECTED]> set search_path = 'unison'; SET [EMAIL PROTECTED]> select 'pseq'::regclass::oid::FQregclass; regclass -- unison.pseq (1 row) -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ./universe -G 6.672e-11 -e 1.602e-19 -protonmass 1.673e-27 -uspres bush kernel warning: universe consuming too many resources. Killing. universe killed due to catastrophic leadership. Try -uspres carter. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Bug in CREATE/DROP TABLESPACE command
William Garrison <[EMAIL PROTECTED]> writes: > -- Drop the tablespace and re-create in in an invalid location > -- This only causes the bug if both these commands are run in one batch What do you mean by "one batch" exactly? Both CREATE and DROP TABLESPACE refuse to run in a transaction block, so I'm confused about this. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Bug in CREATE/DROP TABLESPACE command
Not a transaction block. A batch of commands submitted to the server in a single call. In MSSQL land, I call that a batch. I don't know the PostgreSql term. If you are using the pgadmin3 GUI, then I mean pressing F5 once is a single batch. Pressing it twice is two batches. The following will submit this as one batch, and will NOT reproduce the problem: 1) Open pgadmin3. Open the query tool. Paste in the following. CREATE TABLESPACE bad_tablespace LOCATION E'C:\\postgresql\\MyDatabase'; DROP TABLESPACE IF EXISTS bad_tablespace; CREATE TABLESPACE bad_tablespace LOCATION E'Z:\\postgresql\\MyDatabase'; DROP TABLESPACE IF EXISTS bad_tablespace; 2) Press F5 However, the following submits it in 3 batches, and will reproduce the problem: 1) Open pgadmin3. Open the query tool. Paste in the following. CREATE TABLESPACE bad_tablespace LOCATION E'C:\\postgresql\\MyDatabase'; 2) Press F5 3) Delete the text, and replace it with the following: DROP TABLESPACE IF EXISTS bad_tablespace; CREATE TABLESPACE bad_tablespace LOCATION E'Z:\\postgresql\\MyDatabase'; 4) Press F5 5) Delete the text, and replace it with the following: DROP TABLESPACE IF EXISTS bad_tablespace; 6) Press F5 Strangely, I am unable to duplicate the problem with psql. I thought it would submit a single batch if I didn't press enter between each command, but it doesn't seem to work that way. If there is a better term please let me know. Tom Lane wrote: William Garrison <[EMAIL PROTECTED]> writes: -- Drop the tablespace and re-create in in an invalid location -- This only causes the bug if both these commands are run in one batch What do you mean by "one batch" exactly? Both CREATE and DROP TABLESPACE refuse to run in a transaction block, so I'm confused about this. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Deadlock with REINDEX TABLE
Erik Jones <[EMAIL PROTECTED]> writes: > Mar 21 19:36:18 [info] User Info: REINDEX TABLE > emma_messages_email_queue; [nativecode=ERROR: deadlock detected > DETAIL: Process 12912 waits for AccessExclusiveLock on relation > 138763808 of database 16384; blocked by process 15217. > Process 15217 waits for RowExclusiveLock on relation 17111 of > database 16384; blocked by process 12912.] > Relation 138763808 is the primary key index on the table relation 17111. > Proc 12912 is the REINDEX and proc 15217 is the contending query, I'm > assuming it was an update due being a RowExclusive lock. This looks like a lock-upgrade deadlock to me. REINDEX TABLE takes only ShareLock on the table itself, but needs AccessExclusiveLock on each index successively. What I'm guessing happened is that the conflicting transaction did first a SELECT and then an UPDATE on the table; the SELECT would take AccessShare (which it could hold concurrently with the reindex's ShareLock) and then the UPDATE would block because its RowExclusiveLock request has to wait for the ShareLock to release. What's not entirely clear though is why the conflicting transaction had any lock on the index at this point. The UPDATE wouldn't have acquired index locks yet. The only idea that comes to mind is that the SELECT was actually a cursor that was still open at the time of the UPDATE ... does your app do things like that? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Insert fail: could not open relation with OID 3221204992
"Ale Raza" <[EMAIL PROTECTED]> writes: > No problem without shape column. I can load all data. What I thought :-( > My concern is: > - Why it's an issue on Linux not on windows? In both cases it's a window > client. Platform-dependent bug in your code, likely. Without seeing the code it's impossible to speculate much further, but I'd look first for places that scribble on memory not allocated to you (perhaps due to a miscalculation of the size needed for a dynamically-allocated object). regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] multi terabyte fulltext searching
On Wed, 21 Mar 2007 08:57:39 -0700, Benjamin Arai wrote: > Hi Oleg, > > I am currently using GIST indexes because I receive about 10GB of new data > a week (then again I am not deleting any information). The do not expect > to be able to stop receiving text for about 5 years, so the data is not > going to become static any time soon. The reason I am concerned with > performance is that I am providing a search system for several newspapers > since essentially the beginning of time. Many bibliographer etc would > like to use this utility but if each search takes too long I am not going > to be able to support many concurrent users. > > Benjamin > At a previous job, I built a system to do this. We had 3,000 publications and approx 70M newspaper articles. Total content size (postprocessed) was on the order of >100GB, IIRC. We used a proprietary (closed-source not ours) search engine. In order to reach subsecond response time we needed to horizontally scale to about 50-70 machines, each a low-end Dell 1650. This was after about 5 years of trying to vertically scale. -arturo ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] xpath_list() function
On Thu, 22 Mar 2007 14:36:32 +0100, Andy Dale wrote: > > testdb=# SELECT film_name, xpath_list(description, 'year') FROM filminfo; > film_name | xpath_list > --- + > Casablanca | 1942 > Rear Window | 1954 > The Godfather | 1972 > Test film | 1973,1972 > > It would seem reasonable in this example that the 1973,1972 gained from > xpath_list could be reused in a WHERE clause like so > > SELECT film_name WHERE '1973' IN (xpath_list(description, 'year')); > > But the xpath_list function returns a single string so the above query > returns no rows. I can not find a way of splitting the values returned > from xpath_list > so that they can be used in an IN () > clause, can anybody point me in the right direction for For that you're supposed to use xpath_table. I forget the exact syntax but you end up doing something like select film_name from (select xpath_table(...) as FILMS) where film_year = 1973. -arturo ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Bug in CREATE/DROP TABLESPACE command
William Garrison <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> What do you mean by "one batch" exactly? Both CREATE and DROP TABLESPACE >> refuse to run in a transaction block, so I'm confused about this. > Not a transaction block. A batch of commands submitted to the server in > a single call. In MSSQL land, I call that a batch. I don't know the > PostgreSql term. If you are using the pgadmin3 GUI, then I mean pressing > F5 once is a single batch. Pressing it twice is two batches. Oh, it's a single simple-Query message. You could reproduce the problem with psql if you put multiple commands into a "-c" command line switch. This is a basic oversight in PreventTransactionChain: it doesn't reject the case where the command is submitted as part of a multi-query string in a single Query message. This is relatively easy to fix in CVS HEAD --- we can just teach exec_simple_query to pass isTopLevel = true only when the querystring contains a single command, or maybe better only for the last command of a querystring. I don't see any very practical way to fix it in older releases though; at least not anything I'd want to backpatch when it can't be tested first in HEAD. Anyone have an idea about a reasonable back-branch fix? regards, tom lane ---(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] Deadlock with REINDEX TABLE
On Mar 22, 2007, at 1:01 PM, Tom Lane wrote: Erik Jones <[EMAIL PROTECTED]> writes: Mar 21 19:36:18 [info] User Info: REINDEX TABLE emma_messages_email_queue; [nativecode=ERROR: deadlock detected DETAIL: Process 12912 waits for AccessExclusiveLock on relation 138763808 of database 16384; blocked by process 15217. Process 15217 waits for RowExclusiveLock on relation 17111 of database 16384; blocked by process 12912.] Relation 138763808 is the primary key index on the table relation 17111. Proc 12912 is the REINDEX and proc 15217 is the contending query, I'm assuming it was an update due being a RowExclusive lock. This looks like a lock-upgrade deadlock to me. REINDEX TABLE takes only ShareLock on the table itself, but needs AccessExclusiveLock on each index successively. What I'm guessing happened is that the conflicting transaction did first a SELECT and then an UPDATE on the table; the SELECT would take AccessShare (which it could hold concurrently with the reindex's ShareLock) and then the UPDATE would block because its RowExclusiveLock request has to wait for the ShareLock to release. What's not entirely clear though is why the conflicting transaction had any lock on the index at this point. The UPDATE wouldn't have acquired index locks yet. The only idea that comes to mind is that the SELECT was actually a cursor that was still open at the time of the UPDATE ... does your app do things like that? Alas, the guy who wrote most of the app code that works with the table in question is on vacation so the only answer I can give right away is "Maybe, but not likely...". Until I can know for sure, I'll just make sure to only reindex that table during off hours when the likelihood of this happening again is negligible. erik jones <[EMAIL PROTECTED]> software developer 615-296-0838 emma(r)
[GENERAL] making postgres DB stable, efficient and secure
Hi, I'm done with my database design and almost got it working (with all triggers and functions) pefectly. Now, i need to see how can I make my DB stable, efficient and secure. I wanted to know how should I go about it as far as postgres is concerned What are the best practices is this regard. Any kind of help would be higly appreciated. Thanks, ~Jas
Re: [GENERAL] making postgres DB stable, efficient and secure
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/22/07 15:59, Jasbinder Singh Bali wrote: > Hi, > I'm done with my database design and almost got it working (with all > triggers and functions) pefectly. > Now, i need to see how can I make my DB stable, efficient and secure. Your database is not stable You host it on Windows? > > I wanted to know how should I go about it as far as postgres is concerned > What are the best practices is this regard. > > Any kind of help would be higly appreciated. Start with what you do on any database: GRANT/REVOKE and creating indexes that match your queries, updates & deletes without impact inserts. Next is buffer sizing. http://www.postgresql.org/docs/8.2/interactive/ What version are you running? > > Thanks, > ~Jas -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFGAvOhS9HxQb37XmcRAmHNAKCqpB/mTInSjjRmOR3ql+rKXZdW5ACgo208 f6RLxC9ypHHqcMqNAxylNF8= =tsED -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] making postgres DB stable, efficient and secure
On Thu, 2007-03-22 at 16:59 -0400, Jasbinder Singh Bali wrote: > Hi, > I'm done with my database design and almost got it working (with all > triggers and functions) pefectly. > Now, i need to see how can I make my DB stable, efficient and secure. > > I wanted to know how should I go about it as far as postgres is > concerned > What are the best practices is this regard. > > Any kind of help would be higly appreciated. > (1) Run it on a stable OS (2) Disable write cache on your disks, and make *sure* it's disabled The rest of your question is quite open-ended. Start with the default configuration, read the documentation, experiment on a separate box you don't care about, and you're on the right track. Regards, Jeff Davis ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] multi-row check constraints?
On Tue, 2007-03-20 at 13:21 -0700, Angva wrote: > Dear Postgres fans, > > Hi, I was wondering what is the best way to achieve a multi-row check > constraint. For example, you have a table with two columns: ID and > percent, no primary key. The goal is to enforce that all values of > percent, per ID, add up to exactly 100%. I come from an Oracle > background, and what you would probably do on Oracle is create a > materialized view with the sum(percent) grouped by ID, then put a > constraint on the sum column. This problem is also solvable using > triggers, but it's messy and imposes a lot of serialization. Not to > mention easy to get wrong. > > So, I've come across this problem in Postgres and was hoping someone > could steer me in the right direction. > Your Oracle solution is interesting, and can indeed be implemented in PostgreSQL in exactly the same way. Look at materialized views here: http://www.varlena.com/varlena/GeneralBits/Tidbits/matviews.html Another way to do it without using an entire materialized view is to obtain a row level lock on the ID using SELECT ... WHERE id=123 FOR UPDATE. To do this you need to have a table that contains all the IDs and where id has a unique index to prevent race conditions when adding new IDs. What are you trying to do exactly? Why does the table have no primary key? Regards, Jeff Davis ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] multi-row check constraints?
Greetings Select COLUMN FROM TABLE WHERE (some condition) for UPDATE OF COLUMN is not supported? what would happen in a Table Deadlock scenario??? M- --- This e-mail message (including attachments, if any) is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, proprietary , confidential and exempt from disclosure. If you are not the intended recipient, you are notified that any dissemination, distribution or copying of this communication is strictly prohibited. --- Le présent message électronique (y compris les pièces qui y sont annexées, le cas échéant) s'adresse au destinataire indiqué et peut contenir des renseignements de caractère privé ou confidentiel. Si vous n'êtes pas le destinataire de ce document, nous vous signalons qu'il est strictement interdit de le diffuser, de le distribuer ou de le reproduire. - Original Message - From: "Jeff Davis" <[EMAIL PROTECTED]> To: "Angva" <[EMAIL PROTECTED]> Cc: Sent: Thursday, March 22, 2007 8:09 PM Subject: Re: [GENERAL] multi-row check constraints? > On Tue, 2007-03-20 at 13:21 -0700, Angva wrote: >> Dear Postgres fans, >> >> Hi, I was wondering what is the best way to achieve a multi-row check >> constraint. For example, you have a table with two columns: ID and >> percent, no primary key. The goal is to enforce that all values of >> percent, per ID, add up to exactly 100%. I come from an Oracle >> background, and what you would probably do on Oracle is create a >> materialized view with the sum(percent) grouped by ID, then put a >> constraint on the sum column. This problem is also solvable using >> triggers, but it's messy and imposes a lot of serialization. Not to >> mention easy to get wrong. >> >> So, I've come across this problem in Postgres and was hoping someone >> could steer me in the right direction. >> > > Your Oracle solution is interesting, and can indeed be implemented in > PostgreSQL in exactly the same way. Look at materialized views here: > > http://www.varlena.com/varlena/GeneralBits/Tidbits/matviews.html > > Another way to do it without using an entire materialized view is to > obtain a row level lock on the ID using SELECT ... WHERE id=123 FOR > UPDATE. To do this you need to have a table that contains all the IDs > and where id has a unique index to prevent race conditions when adding > new IDs. > > What are you trying to do exactly? Why does the table have no primary > key? > > Regards, > Jeff Davis > > > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > ---(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] Configuring phpPgAdmin and pg_ctl reload
Douglas McNaught wrote: John Meyer <[EMAIL PROTECTED]> writes: Maybe it was just understood, but I was typing in word for word the entry from pg_hba.conf, but I'll keep that for reference later on. pg_ctl needs either the data directory supplied on the command line, or PGDATA set in the environment, which reading the manpage would have told you. ;) -Doug In the end, it got fixed, but that will go into the blog for future reference. Thanks all. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] multi-row check constraints?
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > Hi, I was wondering what is the best way to achieve a multi-row check > constraint. For example, you have a table with two columns: ID and > percent, no primary key. The goal is to enforce that all values of > percent, per ID, add up to exactly 100%. I come from an Oracle > background, and what you would probably do on Oracle is create a > materialized view with the sum(percent) grouped by ID, then put a > constraint on the sum column. This problem is also solvable using > triggers, but it's messy and imposes a lot of serialization. Not to > mention easy to get wrong. I don't think the triggers solution is that bad. The only trick is using an intermediate table so that we don't have to recheck the entire table at the end of the statement: CREATE TABLE hundred ( id INTEGER NULL, percent FLOAT NOT NULL ); CREATE TABLE tracker ( trackid INTEGER ); CREATE FUNCTION percent_one() RETURNS TRIGGER LANGUAGE plpgsql AS $_$ BEGIN IF TG_OP <> 'INSERT' THEN INSERT INTO tracker VALUES (OLD.id); END IF; IF TG_OP <> 'DELETE' THEN INSERT INTO tracker VALUES (NEW.id); END IF; RETURN NULL; END; $_$; CREATE FUNCTION percent_two() RETURNS TRIGGER LANGUAGE plpgsql AS $_$ DECLARE myrec RECORD; badids INTEGER = 0; BEGIN FOR myrec IN SELECT id, sum(percent) AS pc FROM hundred WHERE EXISTS (SELECT 1 FROM tracker WHERE trackid = id) GROUP BY id HAVING sum(percent) <> 100 ORDER BY id LOOP RAISE WARNING 'Percentage on id % sums to %, not 100', myrec.id, myrec.pc; badids = badids + 1; END LOOP; TRUNCATE TABLE tracker; IF badids>=1 THEN RAISE EXCEPTION 'Number of ids not summing to 100: %', badids; END IF; RETURN NULL; END; $_$; CREATE TRIGGER percent_one AFTER INSERT OR UPDATE OR DELETE ON hundred FOR EACH ROW EXECUTE PROCEDURE percent_one(); CREATE TRIGGER percent_two AFTER INSERT OR UPDATE OR DELETE ON hundred FOR EACH STATEMENT EXECUTE PROCEDURE percent_two(); - -- Fails: INSERT INTO hundred SELECT 1,25 UNION ALL SELECT 1,25 UNION ALL SELECT 2,33; - -- Works: INSERT INTO hundred SELECT 1,45 UNION ALL SELECT 1,55; - -- Works: UPDATE hundred SET id=2 where id=1; - -- Fails: UPDATE hundred SET percent=55.5 WHERE percent = 55; - -- Works: INSERT INTO hundred SELECT 3,33.5 UNION ALL SELECT 3,55.5 UNION ALL SELECT 3,11.0; - -- Fails: DELETE FROM hundred WHERE percent = 55.5; - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200703222156 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFGAzQ1vJuQZxSWSsgRA9WIAKCXf3t3MkSj2xoXLoScx3lu0aBwQQCfUiTW is9ZKyAPuzaAvnkMjP0dXEc= =BeQC -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] VACUUM ANALYZE
I see in all the docs to run VACUUM ANALYZE periodically. My host told me that in Postgres 8.2 this is not needed as it is done automatically. Is that true? How can I see the results of the automatic vacuum analyze? Or configure them?
Re: [GENERAL] VACUUM ANALYZE
"Robert James" <[EMAIL PROTECTED]> writes: > I see in all the docs to run VACUUM ANALYZE periodically. My host told me > that in Postgres 8.2 this is not needed as it is done automatically. 8.2 has an autovacuum feature but it is *not* turned on by default ... has your host enabled it? > Is that true? How can I see the results of the automatic vacuum analyze? Or > configure them? See the autovacuum info in the documentation. regards, tom lane ---(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
[GENERAL] How to get the DML Commands exceuted from functions
Hi All, There are a few plpgsql functions in our application, When the functions are called, I would like to see the commands executed from the functions ( the selects, inserts, updates and Deletes written in the function body) I am from SQL server world and there exists a tool SQL Profiler for the same purpose. I am looking for something like that Thanks in advance Anoo.S
Re: [GENERAL] [ADMIN] How to get the DML Commands exceuted from functions
CREATE OR REPLACE FUNCTION xyz() returns int4 as $body$ DECLARE BEGIN IF (TG_OP = 'DELETE') THEN n_id = OLD.campaign_id; EXECUTE 'DELETE FROM cn_mapping WHERE campaign_id = '||quote_literal(n_id); RETURN 0; END IF; END; $body$ LANGUAGE 'plpgsql' VOLATILE; Date: Fri, 23 Mar 2007 10:44:49 +0530From: [EMAIL PROTECTED]: pgsql-admin@postgresql.org; [EMAIL PROTECTED]: [ADMIN] How to get the DML Commands exceuted from functions Hi All, There are a few plpgsql functions in our application, When the functions are called, I would like to see the commands executed from the functions ( the selects, inserts, updates and Deletes written in the function body) I am from SQL server world and there exists a tool SQL Profiler for the same purpose. I am looking for something like that Thanks in advance Anoo.S _ Take a break and play crossword puzzles - FREE! http://games.msn.com/en/flexicon/default.htm?icid=flexicon_ wlmemailtaglinemarch07
