Re: [GENERAL] copy ... from stdin csv; and bytea
David Wilson wrote: On Mon, Jul 28, 2008 at 1:24 AM, Klint Gore [EMAIL PROTECTED] wrote: Try just a single \ e.g. ge.xls,application/vnd.ms-excel,71168,\320\317\021\340\241[snip] Thanks- I did try that, and it at least gave the expected output from select, but is there a way to verify that it's actually handling it correctly rather than simply storing the sequence of characters? I'm not certain how to check the actual byte width of a column within a row, and I'd *really* rather not be storing 4 bytes for every 1 in the binary if I can avoid it- this column is already going to be doubling field width; quadrupling it would give me space headaches I really don't want to deal with. :) select length(bytea_field) from table You could use ||pg_relation_size|(|text|)| or ||pg_total_relation_size|(|text|) |to see how much disk space it takes up. You can play with the storage settings for the column if you want to try and handle the space better. see alter table set storage. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- 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] copy ... from stdin csv; and bytea
Klint Gore [EMAIL PROTECTED] writes: David Wilson wrote: I'm not certain how to check the actual byte width of a column within a row, select length(bytea_field) from table If you want the actual on-disk footprint, use pg_column_size() 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
[GENERAL] why my postgresql auto crashed???
hi, all when I do the command from a sql file by psql client, I got the message: psql:/home/zhay/insert.sql:8: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. psql:/home/zhay/insert.sql:8: connection to server was lost my sql file is : set search_path to lives; insert into store_all select c.*, z.t from ( select b.*, y.t from ( select a.*, x.t from store a left join (select pid, array_to_string(array_accum(anchor), ' ') as t from recommend group by pid)x on x.pid = a.id )b left join (select pid, array_to_string(array_accum(anchor), ' ') as t from tag group by pid)y on y.pid = b.id )c left join (select pid, array_to_string(array_accum(anchor), ' ') as t from categorie group by pid)z on z.pid = c.id; so, I check the pg_log ---- TRAP: FailedAssertion(!(file-curFile = 0), File: buffile.c, Line: 317) LOG: server process (PID 4121) was terminated by signal 6: Aborted LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. HINT: In a moment you should be able to reconnect to the database and repeat your command. LOG: all server processes terminated; reinitializing LOG: database system was interrupted; last known up at 2008-07-27 07:36:16 UTC LOG: database system was not properly shut down; automatic recovery in progress FATAL: the database system is in recovery mode LOG: redo starts at D/3FB00BC0 LOG: record with zero length at D/3FB59898 LOG: redo done at D/3FB59868 LOG: last completed transaction was at log time 2008-07-27 07:40:53.70866+00 LOG: autovacuum launcher started LOG: database system is ready to accept connections thanks all. regards. -- 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] copy ... from stdin csv; and bytea
Tom Lane wrote: Klint Gore [EMAIL PROTECTED] writes: David Wilson wrote: I'm not certain how to check the actual byte width of a column within a row, select length(bytea_field) from table If you want the actual on-disk footprint, use pg_column_size() Size on disk would have the compression from the default storage = extended wouldn't it? I verified it for myself manually anyway. copy (select * from original limit 5) to stdout with csv; create table foo (like original); alter table foo alter column bytea_field set storage external; copy foo from stdin with csv; select |reltoastrelid from pg_class where relanem = 'original' found the file for it and looked at it with a hex viewer. | klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- 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] why my postgresql auto crashed???
Hi When creating a new thread (asking a new question, etc) on a mailing list please create a new message rather than replying to an existing one. It helps people reading the mailing list keep track. so, I check the pg_log ---- TRAP: FailedAssertion(!(file-curFile = 0), File: buffile.c, Line: 317) LOG: server process (PID 4121) was terminated by signal 6: Aborted This `postgres' backend detected a problem and terminated its self by calling abort(). This forced the postmaster to terminate all other backends and restart the whole server. You omitted some very important information from your post, including: - Your PostgreSQL version - Your operating system - The version of your operating system - How you installed PostgreSQL and where you got it from - Whether the problem is repeatable, ie does the server crash every time you issue the problem command? Sometimes? Only ever happened once? -- 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] why my postgresql auto crashed???
I'm sorry for the lack of information given in this mail; Postgresql: 8.3.3 System:Linux 2.6.9-55.ELsmp Install: I compile it myself Others: the same command, it works fine on another machine(called A):( I EXPLAIN the sql machine A and machine B, I found that on machine B, it never use index when command execute, but on machine A, index used:((, so I set enable_seqscan TO off; on machine B, it's works beacause of the usage of index!!! I don't konw why. thanks all, any help is appreciated. On Mon, 2008-07-28 at 16:32 +0800, Craig Ringer wrote: Hi When creating a new thread (asking a new question, etc) on a mailing list please create a new message rather than replying to an existing one. It helps people reading the mailing list keep track. so, I check the pg_log ---- TRAP: FailedAssertion(!(file-curFile = 0), File: buffile.c, Line: 317) LOG: server process (PID 4121) was terminated by signal 6: Aborted This `postgres' backend detected a problem and terminated its self by calling abort(). This forced the postmaster to terminate all other backends and restart the whole server. You omitted some very important information from your post, including: - Your PostgreSQL version - Your operating system - The version of your operating system - How you installed PostgreSQL and where you got it from - Whether the problem is repeatable, ie does the server crash every time you issue the problem command? Sometimes? Only ever happened once? -- 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] PostgreSQL vs FreeBSD 7.0 as regular user
On Friday 25 July 2008, Zoltan Boszormenyi wrote: is there anyone using PostgreSQL on FreeBSD 7.0 starting from scratch? Every day. I compiled 8.3.3 and wanted to run initdb in my home directory but it fails with the error below. How did you install PostgreSQL? -- Kirk Strauser Daycos -- 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] PostgreSQL vs FreeBSD 7.0 as regular user
In article [EMAIL PROTECTED], Greg Smith [EMAIL PROTECTED] wrote: % Looks like the PostgreSQL documentation here ( % http://www.postgresql.org/docs/current/static/kernel-resources.html ) is % now outdated. From http://www.manpages.info/freebsd/sysctl.8.html : % % The -w option has been deprecated and is silently ignored. % % Looks like the correct thing to do here now is to edit the % /etc/sysctl.conf file, then issue: % % /etc/rc.d/sysctl reload I guess this would work, but you can still change variables from the command-line. It's just that -w isn't required any more (i.e., the same command works with or without the -w flag). I'm not sure the docs should change, since -w is still required at least on NetBSD. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] why my postgresql auto crashed???
hi, all when I do the command from a sql file by psql client, I got the message: psql:/home/zhay/insert.sql:8: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. psql:/home/zhay/insert.sql:8: connection to server was lost my sql file is : set search_path to lives; insert into store_all select c.*, z.t from ( select b.*, y.t from ( select a.*, x.t from store a left join (select pid, array_to_string(array_accum(anchor), ' ') as t from recommend group by pid)x on x.pid = a.id )b left join (select pid, array_to_string(array_accum(anchor), ' ') as t from tag group by pid)y on y.pid = b.id )c left join (select pid, array_to_string(array_accum(anchor), ' ') as t from categorie group by pid)z on z.pid = c.id; so, I check the pg_log -#65279;#65279;--- LOG: server process (PID 4121) was terminated by signal 6: Aborted LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. LOG: all server processes terminated; reinitializing LOG: database system was interrupted; last known up at 2008-07-27 07:36:16 UTC LOG: database system was not properly shut down; automatic recovery in progress FATAL: the database system is in recovery mode LOG: redo starts at D/3FB00BC0 LOG: record with zero length at D/3FB59898 LOG: redo done at D/3FB59868 LOG: last completed transaction was at log time 2008-07-27 07:40:53.70866+00 LOG: autovacuum launcher started LOG: database system is ready to accept connections #65279;#65279; thanks all. regards. ___ 雅虎邮箱，您的终生邮箱！ http://cn.mail.yahoo.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] why my postgresql auto crashed???
hi, all when I do the command from a sql file by psql client, I got the message: psql:/home/zhay/insert.sql:8: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. psql:/home/zhay/insert.sql:8: connection to server was lost my sql file is : set search_path to lives; insert into store_all select c.*, z.t from ( select b.*, y.t from ( select a.*, x.t from store a left join (select pid, array_to_string(array_accum(anchor), ' ') as t from recommend group by pid)x on x.pid = a.id )b left join (select pid, array_to_string(array_accum(anchor), ' ') as t from tag group by pid)y on y.pid = b.id )c left join (select pid, array_to_string(array_accum(anchor), ' ') as t from categorie group by pid)z on z.pid = c.id; so, I check the pg_log -#65279;#65279;--- LOG: server process (PID 4121) was terminated by signal 6: Aborted LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. LOG: all server processes terminated; reinitializing LOG: database system was interrupted; last known up at 2008-07-27 07:36:16 UTC LOG: database system was not properly shut down; automatic recovery in progress FATAL: the database system is in recovery mode LOG: redo starts at D/3FB00BC0 LOG: record with zero length at D/3FB59898 LOG: redo done at D/3FB59868 LOG: last completed transaction was at log time 2008-07-27 07:40:53.70866+00 LOG: autovacuum launcher started LOG: database system is ready to accept connections #65279;#65279; thanks all. regards. ___ 雅虎邮箱，您的终生邮箱！ http://cn.mail.yahoo.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] why my postgresql auto crashed???
Yi Zhao wrote: I'm sorry for the lack of information given in this mail; Postgresql: 8.3.3 System:Linux 2.6.9-55.ELsmp Install: I compile it myself Others: the same command, it works fine on another machine(called A):( I EXPLAIN the sql machine A and machine B, I found that on machine B, it never use index when command execute, but on machine A, index used:((, so I set enable_seqscan TO off; on machine B, it's works beacause of the usage of index!!! I don't konw why. It almost certainly means that you have a corrupt index. First, stop the server and make a full backup of your data directory. Then start the server back up. Do a full pg_dump if you can. Now, try issuing a REINDEX on the problem index. If you don't know which one, or want to make sure, use REINDEX DATABASE instead. Re-test and see if the problem still occurs. You should also check your RAID controller, disks, and filesystem to make sure there's no problem in your storage system. If you are not using a RAID controller with battery backup cache, make sure write caching is turned off on the controller and the disks. If you are not using a UPS, consider getting one. By the way, a Google search (while I was trying to figure out which distro and version of the distro you were using) turned this up: http://www.centos.org/modules/newbb/viewtopic.php?topic_id=8779forum=27 The crash is in ext3, and was triggered by MySQL. See: http://bugs.centos.org/view.php?id=2077 So: check your system logs for errors from the kernel. Consider upgrading to a less ancient kernel, too. -- 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
R: [GENERAL] How to get the real postgreql error from visual basic
-Messaggio originale- Da: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Per conto di dfx Inviato: domenica 27 luglio 2008 19.37 A: pgsql-general@postgresql.org Oggetto: [GENERAL] How to get the real postgreql error from visual basic Dear Sirs, when I execute a function that returns an error, visual basic shows always the same error code ( -214767259) but I would like to know the real postgres code of the error. The visual basic code that I use is the following: Dim Cmd As new ADODB.Command Cmd.CommandText = delete from tablename where id=some_number; Cmd.ActiveConnection = mvarConnection Cmd.Execute Whichever error appens the visual basic Err object returns the same number. What I have to do? Any suggestion will be appreciated. Domenico Hi, I use GetODBCerrors function (which I found somewhere in internet) to return the errors I get back from PostgreSQL. Hope this help dim m_Dbh As ADODB.Connection dim m_LastError as String ... Run query ... If m_Dbh.Errors.Count 0 Then m_LastError = GetODBCerrors Function GetODBCerrors() As String On Error GoTo GetODBCerrors_ErrHandler GetODBCerrors = Dim objError As ADODB.Error Dim strError As String If m_Dbh.Errors.Count 0 Then For Each objError In m_Dbh.Errors strError = strError Error # objError.Number objError.Description vbCrLf NativeError: _ objError.NativeError vbCrLf SQLState: objError.SQLState vbCrLf Reported by: _ objError.Source vbCrLf Help file: objError.HelpFile vbCrLf Help Context ID: _ objError.HelpContext Next GetODBCerrors = strError End If Exit Function GetODBCerrors_ErrHandler: GetODBCerrors = Err.Number Err.Source Err.Description End Function Paolo Saudin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Setting up the postgres codebase in Eclipse
Hello I have searched for help in setting up the code in the eclipse IDE. but hardly found any. If someone who is using eclipse can give a brief HOW TO, it would be really great. Thanks, Abhirama
[GENERAL] Date index not used when selecting a date range
I'm wondering why this index is not used for my query. This is the index: CREATE INDEX idx_stat_date_node_type ON public.stat USING btree (date, node, type); When quering an exact date, it is used explain SELECT * FROM public.stat WHERE node = '1010101010' AND date = '2008-01-01' Index Scan using idx_stat_date_node_type on stat (cost=0.00..279.38 rows=150 width=146) Index Cond: ((date = '2008-01-01'::date) AND ((node)::text = '1010101010'::text)) But when selecting a date range I get this explain SELECT * FROM public.stat WHERE node = '1010101010' AND (date = '2008-06-30'::date AND date = '2008-01-01'::date) Bitmap Heap Scan on stat (cost=710.14..179319.44 rows=39174 width=146) Recheck Cond: ((node)::text = '1010101010'::text) Filter: ((date = '2008-06-30'::date) AND (date = '2008-01-01'::date)) - Bitmap Index Scan on idx_stat_node_id (cost=0.00..710.14 rows=55182 width=0) Index Cond: ((node)::text = '1010101010'::text) How can I change my query so it will use the index ? Thanks Poul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Connecting to an existing transaction state.
Hello, I’m in the process of creating a set of scripts for testing certain locking features in an application. What I would like to do: 1. Start a connection from machine-01 through the m01-s1.sql script. 2.While (1) is running, start another transaction on the same database from machine-02 using m02-s1.sql. At this point in time, there are two open transactions on certain tables in the same database. 3. Using m01-s2.sql I would like to execute a certain SQL statement – BUT within the scope of the transaction begun by m01-s1.sql. 4. Current situation: Since there are several .sql scripts, each getting its own connection and executing sql stmts – they are not aware of activities of the other scripts (i.e. the open transactions). 5. What I’d like to do: After a transaction has been started from a machine, I should be able to save the transaction reference (id?) temporarily somewhere. 6. The next statement (new .sql file) that wishes to execute within the scope of the above transaction – should be able to get the transaction reference (id) and latch onto it in its current state. This way it continues to perform as part of a whole – rather than only executing the statements that it had. Any guidance in this will help. Cheers! AlexiG __ Not happy with your email address?. Get the one you really want - millions of new email addresses available now at Yahoo! http://uk.docs.yahoo.com/ymail/new.html -- 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] why my postgresql auto crashed???
Craig Ringer [EMAIL PROTECTED] writes: Yi Zhao wrote: I don't konw why. It almost certainly means that you have a corrupt index. No, because that assert is nowhere near the index code. I think it's a garden-variety bug, but we need a reproducible test case to fix it. 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
[GENERAL] Fetch for Update
I haven't been able to find much information on Fetch for Update. Does 8.3 support this command?? If so, could someone please point out my error in the following? Bob BEGIN SELECT count (p_id.pid.process_id) INTO Proccount FROM p_id.p_id WHERE process_id = new.process_id; Declare procgraphic cursor for select process_id from p_id.p_id where p_id.p_id.p_id_id = proc_count.p_id_id; Begin Fetch first in procgraphic for update Update p_id.p_id set proc_graphic_position = one From graphics.proc_position where graphics.proc_position.proc_count = proccount; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] should i need to install xml library in postgresql inorder to work on xml file?
Hello, I installed postgresql on my sytem,I need to parse an xml file.When I am working on xpath() it is showing an error that function is notfound and i tried almost all xml functions in postgresql but it is giving me the same result.why so ? should I have to install any xml library inorder to avoid this?Please tell me its very important for me. Thanks, Avin.
Re: [GENERAL] Fetch for Update
Bob Pawley wrote: I haven't been able to find much information on Fetch for Update. Does 8.3 support this command?? If so, could someone please point out my error in the following? Bob BEGIN SELECT count (p_id.pid.process_id) INTO Proccount FROM p_id.p_id WHERE process_id = new.process_id; Declare procgraphic cursor for select process_id from p_id.p_id where p_id.p_id.p_id_id = proc_count.p_id_id; Begin Fetch first in procgraphic for update Update p_id.p_id set proc_graphic_position = one From graphics.proc_position where graphics.proc_position.proc_count = proccount; Theres the Select for update http://www.postgresql.org/docs/8.1/static/sql-select.html#SQL-FOR-UPDATE-SHARE which locks the records for the transaction -- 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] should i need to install xml library in postgresql inorder to work on xml file?
On Mon, 2008-07-28 at 09:25 -0700, aravind chandu wrote: Hello, I installed postgresql on my sytem,I need to parse an xml file.When I am working on xpath() it is showing an error that function is notfound and i tried almost all xml functions in postgresql but it is giving me the same result.why so ? should I have to install any xml library inorder to avoid this?Please tell me its very important for me. Thanks, Avin. I fail to see how we can help if you don't provide the errors you are getting. Sincerely, Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- 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] Fetch for Update
On Jul 28, 2008, at 9:16 AM, Bob Pawley wrote: I haven't been able to find much information on Fetch for Update. Does 8.3 support this command?? Postgres doesn't have an explicit FETCH FOR UDPATE. You can either create the cursor with SELECT FOR UPDATE, or UPDATE the row in the cursor using UPDATE ... CURRENT OF. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Clone a database to other machine
Hi All, I'm stuck to an issue while cloning the pgsql database, can you please help, or give any docs to help out. Query - Trying to have same database on two seprate linux servers. One will be used to upport Applications and other will be used for Report generation only. Want to keep both the database in Sync, hourly or nightly. Kindly help to achive the same. Thanks and regards, Manjit Garg Corbus Global Support Team INDIA -- Email: [EMAIL PROTECTED] Phone: +91-120-304-4000, Ext 252 Fax : +91-120-256-7040 Mob : 9810679256 -- CONFIDENTIALITY NOTICE: This message, including any attachments hereto, (collectively the Email Message) is intended solely for the personal and confidential use of the designated recipient(s) and may contain privileged, proprietary, or otherwise private information which may be subject to attorney-client privilege or may constitute inside information protected by law. If the reader of this message is not the intended recipient, you are hereby notified of the following: (i) Any disclosure, printing, copying, or distribution of this Email Message by you or (ii) the taking of any action by you based on the contents of this Email Message or (iii) any other use of this Email Message by you, are strictly prohibited. If you have received this message in error, please notify the sender immediately and remove all traces of the electronic mail message and its attachments from your system.
[GENERAL] errors while working on xml functions in postgresql
Hello,
I
installed postgresql on my sytem,I need to parse an xml file.When I am
working on xpath() it is showing an error that function is notfound and
i tried almost all xml functions in postgresql but it is giving me the
same result.why so ? should I have to install any xml library inorder
to avoid this?Please tell me its very important for me.
these were the errors which i got
SELECT xpath('/my:a/text()', 'my:a
xmlns:my=http://example.com;test/my:a', ARRAY[ARRAY['my',
'http://example.com']]);
ERROR: function xpath(unknown, unknown, text[]) does not exist at
character 8
HINT: No function matches the given name and argument types. You may need to
add explicit type casts.
STATEMENT: SELECT xpath('/my:a/text()', 'my:a
xmlns:my=http://example.com;test/my:a', ARRAY[ARRAY['my',
'http://example.com']]);
ERROR: function xpath(unknown, unknown, text[]) does not exist
LINE 1: SELECT xpath('/my:a/text()', 'my:a xmlns:my=http://example...
^
HINT: No function matches the given name and argument types. You may need to
add explicit type casts.
Thanks,
Avin.
Re: [GENERAL] Clone a database to other machine
On 12:44 pm 07/28/08 Garg, Manjit [EMAIL PROTECTED] wrote: I'm stuck to an issue while cloning the pgsql database, can you please help, or give any docs to help out. What is the issue? Query - Trying to have same database on two seprate linux servers. Have you been able to pg_dump from the source DB and then read it in the target? Are you trying to do replication or you just want point in times? How large is the database? -- 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] Date index not used when selecting a date range
On 9:09 am 07/28/08 Poul Møller Hansen [EMAIL PROTECTED] wrote: But when selecting a date range I get this explain SELECT * FROM public.stat WHERE node = '1010101010' AND (date = '2008-06-30'::date AND date = '2008-01-01'::date) Bitmap Heap Scan on stat (cost=710.14..179319.44 rows=39174 width=146) Recheck Cond: ((node)::text = '1010101010'::text) Filter: ((date = '2008-06-30'::date) AND (date = '2008-01-01'::date)) - Bitmap Index Scan on idx_stat_node_id (cost=0.00..710.14 rows=55182 width=0) Index Cond: ((node)::text = '1010101010'::text) You may want to do an explain analyze on the query. That would help others help you. Have you run analyze on the table? How selective is the condition node = '1010101010' and the date range. In particular, do you have an idea what percentange of the table fits into that date range? What about the type column? You have it in the index, but not in your query. Have you tried adding type to the query? Will that make it more selective? -- 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] should i need to install xml library in postgresql inorder to work on xml file?
On 28/07/2008 17:25, aravind chandu wrote: I installed postgresql on my sytem,I need to parse an xml file.When I am working on xpath() it is showing an error that function is notfound and i tried almost all xml functions in postgresql but it is giving me the same result.why so ? should I have to install any xml library inorder to avoid this?Please tell me its very important for me. A five-second scan of the docs reveals the following: Use of many of these functions requires the installation to have been built with configure --with-libxml.[1] Are you in a position to see whether this was done on your installation? Ray. [1] http://www.postgresql.org/docs/8.3/static/functions-xml.html -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] should i need to install xml library in postgresql inorder to work on xml file?
On Mon, 2008-07-28 at 18:36 +0100, Raymond O'Donnell wrote: On 28/07/2008 17:25, aravind chandu wrote: I installed postgresql on my sytem,I need to parse an xml file.When I am working on xpath() it is showing an error that function is notfound and i tried almost all xml functions in postgresql but it is giving me the same result.why so ? should I have to install any xml library inorder to avoid this?Please tell me its very important for me. A five-second scan of the docs reveals the following: Use of many of these functions requires the installation to have been built with configure --with-libxml.[1] Are you in a position to see whether this was done on your installation? Running pg_config will give him the info. Joshua D. Drake Ray. [1] http://www.postgresql.org/docs/8.3/static/functions-xml.html -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- 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] errors while working on xml functions in postgresql
aravind chandu [EMAIL PROTECTED] writes: I installed postgresql on my sytem,I need to parse an xml file.When I am working on xpath() it is showing an error that function is notfound and i tried almost all xml functions in postgresql but it is giving me the same result.why so ? Are you reading a manual that corresponds to the PG version you're using? The xml functions are only in core as of 8.3; in prior versions they were in a contrib module, which you'd need to build and install. The contrib functions were a bit different in detail, too, which is why you'd better consult the right manual. should I have to install any xml library inorder to avoid this? No PG version is going to support any xml functionality without libxml2. 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
[GENERAL] Re: should i need to install xml library in postgresql inorder to work on xml file?
On Mon, Jul 28, 2008 at 06:36:48PM +0100, Raymond O'Donnell [EMAIL PROTECTED] wrote a message of 30 lines which said: Are you in a position to see whether this was done on your installation? Side question: is there any way (SELECT * FROM pg_compilation_options?) to retrieve this information from an already installed PostgreSQL? I scanned the documentation for more than five seconds and found nothing. (On my Debian, I've read the source of the Debian package, there must be a better way.) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: should i need to install xml library in postgresql inorder to work on xml file?
On Mon, Jul 28, 2008 at 06:47:43PM +0100, Stephane Bortzmeyer [EMAIL PROTECTED] wrote a message of 19 lines which said: Side question: is there any way (SELECT * FROM pg_compilation_options?) to retrieve this information from an already installed PostgreSQL? I scanned the documentation for more than five seconds and found nothing. OK, found. % pg_config BINDIR = /usr/lib/postgresql/8.3/bin DOCDIR = /usr/share/doc/postgresql-doc-8.3 INCLUDEDIR = /usr/include/postgresql PKGINCLUDEDIR = /usr/include/postgresql INCLUDEDIR-SERVER = /usr/include/postgresql/8.3/server LIBDIR = /usr/lib PKGLIBDIR = /usr/lib/postgresql/8.3/lib LOCALEDIR = /usr/share/locale MANDIR = /usr/share/postgresql/8.3/man SHAREDIR = /usr/share/postgresql/8.3 SYSCONFDIR = /etc/postgresql-common PGXS = /usr/lib/postgresql/8.3/lib/pgxs/src/makefiles/pgxs.mk CONFIGURE = '--build=i486-linux-gnu' '--prefix=/usr' '--includedir=/usr/include' '--mandir=/usr/share/man' '--infodir=/usr/share/info' '--sysconfdir=/etc' '--localstatedir=/var' '--libexecdir=/usr/lib/postgresql-8.3' '--disable-maintainer-mode' '--disable-dependency-tracking' '--srcdir=.' '--mandir=/usr/share/postgresql/8.3/man' '--with-docdir=/usr/share/doc/postgresql-doc-8.3' '--sysconfdir=/etc/postgresql-common' '--datadir=/usr/share/postgresql/8.3' '--bindir=/usr/lib/postgresql/8.3/bin' '--includedir=/usr/include/postgresql/' '--enable-nls' '--enable-integer-datetimes' '--enable-thread-safety' '--enable-debug' '--disable-rpath' '--with-tcl' '--with-perl' '--with-python' '--with-pam' '--with-krb5' '--with-gssapi' '--with-openssl' '--with-libxml' '--with-libxslt' '--with-ldap' '--with-ossp-uuid' '--with-gnu-ld' '--with-tclconfig=/usr/lib/tcl8.4' '--with-tkconfig=/usr/lib/tk8.4' '--with-includes=/usr/include/tcl8.4' '--with-system-tzdata=/usr/share/zoneinfo' '--with-pgport=5432' 'CFLAGS=-g -O2 -g -Wall -O2 -fPIC' 'LDFLAGS=-Wl,-Bsymbolic-functions -Wl,--as-needed' 'CC=cc' 'CPPFLAGS=' 'build_alias=i486-linux-gnu' CC = cc CPPFLAGS = -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include/tcl8.4 CFLAGS = -g -O2 -g -Wall -O2 -fPIC -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -g CFLAGS_SL = -fpic LDFLAGS = -Wl,-Bsymbolic-functions -Wl,--as-needed LDFLAGS_SL = LIBS = -lpgport -lxslt -lxml2 -lpam -lssl -lcrypto -lkrb5 -lcom_err -lgssapi_krb5 -lz -lreadline -lcrypt -ldl -lm VERSION = PostgreSQL 8.3.3 -- 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] Getting data from Xml to Postgresql database
aravind chandu wrote: Hi, I have some data in XML format and i need to upload in postgresql database using stored procedure. Can someone tell me the step by step procedure of the same as i will be doing it for the first time. Thank You, Avin. I have offered a python script that converts XML documents to psql COPY. It is part of this package: http://pgfoundry.org/projects/mstopsql/ As of yet I have been unable to get a password for the web page from pgfoundry so there is information on usage here: http://gwynux.ca/psql/ Gwyneth
Re: [GENERAL] Re: should i need to install xml library in postgresql inorder to work on xml file?
On Mon, Jul 28, 2008 at 06:47:43PM +0100, Stephane Bortzmeyer wrote: Side question: is there any way (SELECT * FROM pg_compilation_options?) to retrieve this information from an already installed PostgreSQL? I scanned the documentation for more than five pg_config. I think it may be in a separate package on Debian. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] array_accum() and quoted content
Some time ago, I found the aggregate function array_accum() listed on
the PostgreSQL web site on a page similar to
http://www.postgresql.org/docs/8.2/static/xaggr.html , and implemented
it in a database that hasn't seen much use. More recently, for a client,
I again used the function but I'm running into some inconsistencies
within a select query in which I'm using the aggregate. The problem is
that sometimes the data contained in the array is quoted, and other
times it isn't, all within the same query results. My returned data may
appear like this:
accumed_column
{test 1,test 2,test 3}
{test4,test5,test6}
The only difference I can see is that the quotes don't appear when the
values returned don't contain white space, and do when white space is
present. Is there any way to force consistency? My PHP code currently is
expecting quoted strings to be returned.
Thank you,
Raymond
--
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] array_accum() and quoted content
Raymond C. Rodgers [EMAIL PROTECTED] writes: The only difference I can see is that the quotes don't appear when the values returned don't contain white space, and do when white space is present. That is per the definition of array output format: http://www.postgresql.org/docs/8.2/static/arrays.html#AEN5876 Is there any way to force consistency? My PHP code currently is expecting quoted strings to be returned. Better fix your PHP code. 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] array_accum() and quoted content
Tom Lane wrote: Raymond C. Rodgers [EMAIL PROTECTED] writes: The only difference I can see is that the quotes don't appear when the values returned don't contain white space, and do when white space is present. That is per the definition of array output format: http://www.postgresql.org/docs/8.2/static/arrays.html#AEN5876 Is there any way to force consistency? My PHP code currently is expecting quoted strings to be returned. Better fix your PHP code. regards, tom lane Drat, thanks. Other than array_accum() I've never used arrays in PostgreSQL, so I wasn't aware of that behavior. Raymond
[GENERAL] How to give input a file for a stored procedure
Hi, I am writing a stored procedure where the input to it is a file.I did not have any idea of how to give input as a file for a stored procedure.could you please help me. Thank You, Avin.
[GENERAL] Must be table owner to truncate?
Hello all, I am trying to GRANT truncate permissions to a non-owner of table and it's not allowing me to: GRANT TRUNCATE ON stage01 TO jaime44; ERROR: unrecognized privilege type truncate How do I grant said permission? Thanks...Michelle. -- View this message in context: http://www.nabble.com/Must-be-table-owner-to-truncate--tp18697753p18697753.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] array_accum() and quoted content
Raymond C. Rodgers escribió: Drat, thanks. Other than array_accum() I've never used arrays in PostgreSQL, so I wasn't aware of that behavior. Why do you want to use array_accum() in the first place? Maybe there are better ways to do what you are using it for, that do not subject you to the awkward ways of arrays. -- 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] Must be table owner to truncate?
smiley2211 [EMAIL PROTECTED] writes: GRANT TRUNCATE ON stage01 TO jaime44; ERROR: unrecognized privilege type truncate There is no such permission; where did you get the idea there was? 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] Must be table owner to truncate?
According to the documentation, http://www.postgresql.org/docs/current/interactive/sql-truncate.html , only the owner can truncate a table. Which means the non-owner must either log in/ switch roles as the owner, or they can just run a DELETE. -Said smiley2211 wrote: Hello all, I am trying to GRANT truncate permissions to a non-owner of table and it's not allowing me to: GRANT TRUNCATE ON stage01 TO jaime44; ERROR: unrecognized privilege type truncate How do I grant said permission? Thanks...Michelle. -- View this message in context: http://www.nabble.com/Must-be-table-owner-to-truncate--tp18697753p18697753.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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
[GENERAL] oscon booth report!
The Pg booth at OSCON this year was a success. We had quite a few volunteers for booth duty! We talked to a *lot* of people, sold some shirts, and Michael B taught us to make balloon animals. More details will be posted to -advocacy. Many, many thanks to everyone who helped make the Pg booth a success: Josh Berkus Robert Bernier Michael Brewer Selena Deckelmann Josh Drake Michael Ewan Dan Langille Rob Lemley Christophe Pettus Tom Raney Greg Smith David Wheeler If I forgot you, please accept my most sincere apologies - I plead conference brain. :) Here are some photos from flickr (not mine): http://www.flickr.com/photos/bitpusher/2696574958/ http://www.flickr.com/photos/seokchanyun/2698606943/ gabrielle -- 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] Must be table owner to truncate?
Unfortunately, I found the command via google...I later checked the documentation... http://www.postgresql.org/docs/8.1/static/sql-truncate.html Thanks...Michelle Tom Lane-2 wrote: smiley2211 [EMAIL PROTECTED] writes: GRANT TRUNCATE ON stage01 TO jaime44; ERROR: unrecognized privilege type truncate There is no such permission; where did you get the idea there was? 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 -( -- View this message in context: http://www.nabble.com/Must-be-table-owner-to-truncate--tp18697753p18698506.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] array_accum() and quoted content
Alvaro Herrera wrote: Raymond C. Rodgers escribió: Drat, thanks. Other than array_accum() I've never used arrays in PostgreSQL, so I wasn't aware of that behavior. Why do you want to use array_accum() in the first place? Maybe there are better ways to do what you are using it for, that do not subject you to the awkward ways of arrays. I'm not a database professional, so I'll explain this as best I can. There are two tables that are linked via entries in a third: company, publisher, and company-publisher association. A publisher can be referenced by multiple companies, so the company-publisher association table is a simple two column table that consists of foreign keyed references to the company table's primary key and the publisher table's primary key. The query in which I'm using array_accum() is building a list of companies and the associated publishers for each. For example: SELECT c.company_id, c.company_name, array_accum(p.publisher_name) AS publishers FROM company_table c LEFT JOIN company_publisher_assoc cpa ON c.company_id = cpa.company_id LEFT JOIN publisher_table p ON cpa.publisher_id = p.publisher_id GROUP BY c.company_id, c.company_name ORDER BY company_name (This query isn't direct out of my code, and thus may have errors, but it should convey the idea of what I'm trying to accomplish.) The result is that I should have a single row containing the company_id, company_name, and publishers' names if any. Thanks, Raymond
[GENERAL] Creating a comprehensive search that queries multiple tables
Hi all,
I am attempting to build an application which returns a list of items where
some piece of information associated with the item matches the entered text.
My database is set up in the following way:
CREATE TABLE public.items(
id int4 NOT NULL DEFAULT nextval('items_id_seq'::regclass),
name varchar(255) NOT NULL DEFAULT 'Unknown'::character varying,
description_id int4 ,
PRIMARY KEY (id)
);
CREATE TABLE public.notes(
id int4 NOT NULL DEFAULT nextval('notes_id_seq'::regclass),
item_id int4 ,
kind varchar(255) NOT NULL ,
event_id int4 ,
text text NOT NULL ,
PRIMARY KEY (id)
) ;
CREATE TABLE public.item_categories(
id int4 NOT NULL DEFAULT nextval('item_categories_id_seq'::regclass),
item_id int4 ,
category_id int4 ,
association varchar(255) DEFAULT NULL::character varying,
PRIMARY KEY (id)
);
CREATE TABLE public.categories(
id int4 NOT NULL DEFAULT nextval('categories_id_seq'::regclass),
name varchar(255) NOT NULL ,
PRIMARY KEY (id)
);
CREATE TABLE public.events(
id int4 NOT NULL DEFAULT nextval('events_id_seq'::regclass),
association varchar(255) DEFAULT NULL::character varying,
item_id int4 ,
PRIMARY KEY (id)
) ;
CREATE TABLE public.event_locations(
id int4 NOT NULL DEFAULT nextval('event_locations_id_seq'::regclass),
event_id int4 ,
location_id int4 ,
association varchar(255) DEFAULT NULL::character varying,
PRIMARY KEY (id)
);
CREATE TABLE public.event_people(
id int4 NOT NULL DEFAULT nextval('event_people_id_seq'::regclass),
event_id int4 ,
person_id int4 ,
association varchar(255) DEFAULT NULL::character varying,
PRIMARY KEY (id)
);
CREATE TABLE public.people(
id int4 NOT NULL DEFAULT nextval('people_id_seq'::regclass),
first_name varchar(255) DEFAULT NULL::character varying,
middle_name varchar(255) DEFAULT NULL::character varying,
last_name varchar(255) DEFAULT NULL::character varying,
suffix varchar(255) DEFAULT NULL::character varying,
prefix varchar(255) DEFAULT NULL::character varying,
culture varchar(255) DEFAULT NULL::character varying,
description text ,
PRIMARY KEY (id)
) ;
CREATE TABLE public.locations(
id int4 NOT NULL DEFAULT nextval('locations_id_seq'::regclass),
name varchar(255) DEFAULT NULL::character varying,
PRIMARY KEY (id)
) WITHOUT OIDS;
Items have many notes and events, and events in turn have many people and
locations associated with them. For example, if a user types in the word
'dog', I would like to be able to find all items which were either made in
'Dog River', made by 'Dog the Bounty Hunter', have the name 'dog collar', or
have a note attached to them stating that they were 'used to tame wild
dogs'.
I would also like to know which field the text was matched in. The data
associated with the item doesn't change very often, so I was thinking of
creating a tsvector column in the items table which holds all the text
associated with each item, for the purpose of searching. I am not sure how I
would be able to figure out which field was matched using this approach,
however. Does anyone have any thoughts or ideas on this issue?
Any pokes in the right direction would be much appreciated.
Thanks,
Ryan Wallace
Re: [GENERAL] Clone a database to other machine
On 1:32 pm 07/28/08 Garg, Manjit [EMAIL PROTECTED] wrote: But, actually I want to keep both the Databse in Sync. I want clone db to get the data from Master in certain intervals. Sounds like you are looking for replication. Check http://wiki.postgresql.org/wiki/Replication%2C_Clustering%2C_and_Connection_Pooling -- 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] array_accum() and quoted content
Raymond C. Rodgers escribió: The query in which I'm using array_accum() is building a list of companies and the associated publishers for each. For example: SELECT c.company_id, c.company_name, array_accum(p.publisher_name) AS publishers FROM company_table c LEFT JOIN company_publisher_assoc cpa ON c.company_id = cpa.company_id LEFT JOIN publisher_table p ON cpa.publisher_id = p.publisher_id GROUP BY c.company_id, c.company_name ORDER BY company_name (This query isn't direct out of my code, and thus may have errors, but it should convey the idea of what I'm trying to accomplish.) The result is that I should have a single row containing the company_id, company_name, and publishers' names if any. In order to do this you can use a custom aggregate function to concatenate the texts. I have described this previously here: http://archives.postgresql.org/message-id/20080327234052.GZ8764%40alvh.no-ip.org the text is in spanish but the SQL commands should be trivial to follow. I think this is a FAQ. -- 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] array_accum() and quoted content
Alvaro Herrera wrote: Raymond C. Rodgers escribió: The query in which I'm using array_accum() is building a list of companies and the associated publishers for each. For example: SELECT c.company_id, c.company_name, array_accum(p.publisher_name) AS publishers FROM company_table c LEFT JOIN company_publisher_assoc cpa ON c.company_id = cpa.company_id LEFT JOIN publisher_table p ON cpa.publisher_id = p.publisher_id GROUP BY c.company_id, c.company_name ORDER BY company_name (This query isn't direct out of my code, and thus may have errors, but it should convey the idea of what I'm trying to accomplish.) The result is that I should have a single row containing the company_id, company_name, and publishers' names if any. In order to do this you can use a custom aggregate function to concatenate the texts. I have described this previously here: http://archives.postgresql.org/message-id/20080327234052.GZ8764%40alvh.no-ip.org the text is in spanish but the SQL commands should be trivial to follow. I think this is a FAQ. Thanks for the link, and the SQL is simple enough to follow. I'll give it a whirl. It would certainly be useful to have that SQL posted as a comment on the PostgreSQL documentation page I referenced earlier; maybe it could stop being a FAQ, and end up a FFA (Frequently Found Answer) :-) Thanks again, Raymond
Re: [GENERAL] array_accum() and quoted content
On Mon, Jul 28, 2008 at 04:11:26PM -0400, Raymond C. Rodgers wrote: Alvaro Herrera wrote: Raymond C. Rodgers escribió: Drat, thanks. Other than array_accum() I've never used arrays in PostgreSQL, so I wasn't aware of that behavior. Why do you want to use array_accum() in the first place? Maybe there are better ways to do what you are using it for, that do not subject you to the awkward ways of arrays. I'm not a database professional, so I'll explain this as best I can. There are two tables that are linked via entries in a third: company, publisher, and company-publisher association. A publisher can be referenced by multiple companies, so the company-publisher association table is a simple two column table that consists of foreign keyed references to the company table's primary key and the publisher table's primary key. The query in which I'm using array_accum() is building a list of companies and the associated publishers for each. For example: SELECT c.company_id, c.company_name, array_accum(p.publisher_name) AS publishers FROM company_table c LEFT JOIN company_publisher_assoc cpa ON c.company_id = cpa.company_id LEFT JOIN publisher_table p ON cpa.publisher_id = p.publisher_id GROUP BY c.company_id, c.company_name ORDER BY company_name You could do something like array_to_string( array_accum(p.publisher_name), '|' -- or any other string guaranteed not to appear in the publisher_name ) Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] why my postgresql auto crashed???
yes, I thinks it is unrelated with index, beacause the problem is still exist after reindex. I try to change the work memory or shared memory, it's no use:( regards, Yi On Mon, 2008-07-28 at 11:10 -0400, Tom Lane wrote: Craig Ringer [EMAIL PROTECTED] writes: Yi Zhao wrote: I don't konw why. It almost certainly means that you have a corrupt index. No, because that assert is nowhere near the index code. I think it's a garden-variety bug, but we need a reproducible test case to fix it. 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] why my postgresql auto crashed???
Yi Zhao wrote: yes, I thinks it is unrelated with index, beacause the problem is still exist after reindex. I try to change the work memory or shared memory, it's no use:( Of course not. Please post the schema of involved tables, and enough data in them to be able to reproduce the problem. -- 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
[GENERAL] why can't I load pgxml.sql
1. ./configure --with-libxml --with-libxslt 2. makemake install (successful) 3. test=# \i /usr/local/pgsql/share/contrib/pgxml.sql error appearance: SET psql:/usr/local/pgsql/share/contrib/pgxml.sql:10: ERROR: could not load library /usr/local/pgsql/lib/pgxml.so: /usr/lib/libxml2.so.2: undefined symbol: gzopen64 psql:/usr/local/pgsql/share/contrib/pgxml.sql:15: ERROR: could not load library /usr/local/pgsql/lib/pgxml.so: /usr/lib/libxml2.so.2: undefined symbol: gzopen64 psql:/usr/local/pgsql/share/contrib/pgxml.sql:19: ERROR: could not load library /usr/local/pgsql/lib/pgxml.so: /usr/lib/libxml2.so.2: undefined symbol: gzopen64 psql:/usr/local/pgsql/share/contrib/pgxml.sql:23: ERROR: could not load library /usr/local/pgsql/lib/pgxml.so: /usr/lib/libxml2.so.2: undefined symbol: gzopen64 psql:/usr/local/pgsql/share/contrib/pgxml.sql:27: ERROR: could not load library /usr/local/pgsql/lib/pgxml.so: /usr/lib/libxml2.so.2: undefined symbol: gzopen64 psql:/usr/local/pgsql/share/contrib/pgxml.sql:31: ERROR: could not load library /usr/local/pgsql/lib/pgxml.so: /usr/lib/libxml2.so.2: undefined symbol: gzopen64 psql:/usr/local/pgsql/share/contrib/pgxml.sql:35: ERROR: could not load library /usr/local/pgsql/lib/pgxml.so: /usr/lib/libxml2.so.2: undefined symbol: gzopen64 psql:/usr/local/pgsql/share/contrib/pgxml.sql:41: ERROR: could not load library /usr/local/pgsql/lib/pgxml.so: /usr/lib/libxml2.so.2: undefined symbol: gzopen64 psql:/usr/local/pgsql/share/contrib/pgxml.sql:46: ERROR: function xpath_list(text, text, unknown) does not exist LINE 2: AS 'SELECT xpath_list($1,$2,'','')' ^ 提示: No function matches the given name and argument types. You might need to add explicit type casts. psql:/usr/local/pgsql/share/contrib/pgxml.sql:56: ERROR: function xpath_nodeset(text, text, unknown, unknown) does not exist LINE 3: AS 'SELECT xpath_nodeset($1,$2,,)' ^ 提示: No function matches the given name and argument types. You might need to add explicit type casts. psql:/usr/local/pgsql/share/contrib/pgxml.sql:62: ERROR: function xpath_nodeset(text, text, unknown, text) does not exist LINE 3: AS 'SELECT xpath_nodeset($1,$2,,$3)' ^ 提示: No function matches the given name and argument types. You might need to add explicit type casts. psql:/usr/local/pgsql/share/contrib/pgxml.sql:69: ERROR: could not load library /usr/local/pgsql/lib/pgxml.so: /usr/lib/libxml2.so.2: undefined symbol: gzopen64 psql:/usr/local/pgsql/share/contrib/pgxml.sql:79: ERROR: could not load library /usr/local/pgsql/lib/pgxml.so: /usr/lib/libxml2.so.2: undefined symbol: gzopen64 psql:/usr/local/pgsql/share/contrib/pgxml.sql:86: ERROR: could not load library /usr/local/pgsql/lib/pgxml.so: /usr/lib/libxml2.so.2: undefined symbol: gzopen64 thanks all. regards. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] a SQL query question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, I have a table of the form aid pid nmol - --- --- 123 34 245 3445 323 100 478 12 545 14 645 200 7null null In general, aid is unique, pid and nmol are non-unique. What I'm trying to do is to select those rows where pid is not null, grouped by pid. So I'd get the following aid pid nmol - --- --- 123 34 323 100 245 3445 545 14 645 200 478 12 From within each group I'd like to select the row that has the maximum value of nmol. So I'd end up with aid pid nmol - --- --- 323 100 245 3445 478 12 I can easily do the first step, but am struggling to make the SQL for the second step. Any pointers would be appreciated - --- Rajarshi Guha [EMAIL PROTECTED] GPG Fingerprint: D070 5427 CC5B 7938 929C DD13 66A1 922C 51E7 9E84 - --- whois awk?, sed Grep. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.8 (Darwin) iEYEARECAAYFAkiOfd4ACgkQZqGSLFHnnoSOKACguioqdY0/Ut7su2KUYu+IRP7D xOUAoKZsQKveWM52RTe422i3SRGWZk2u =Xs+n -END PGP SIGNATURE- -- 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] why can't I load pgxml.sql
Yi Zhao [EMAIL PROTECTED] writes: 1. ./configure --with-libxml --with-libxslt 2. makemake install (successful) 3. test=# \i /usr/local/pgsql/share/contrib/pgxml.sql error appearance: SET psql:/usr/local/pgsql/share/contrib/pgxml.sql:10: ERROR: could not load library /usr/local/pgsql/lib/pgxml.so: /usr/lib/libxml2.so.2: undefined symbol: gzopen64 Well, you've still failed to mention most all of the relevant information, like what platform this is and what PG version. But what the above suggests is that libxml2 is linked to a version of libz that doesn't appear anywhere in the dynamic linker's search path. How did you obtain or build libxml2, exactly? If this is a Linux platform, I think you might have missed out a ldconfig call or two. 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] a SQL query question
Rajarshi Guha wrote: What I'm trying to do is to select those rows where pid is not null, grouped by pid. From within each group I'd like to select the row that has the maximum value of nmol. Distinct on should do the job for you. select distinct on (pid) aid, pid, nmol from atable where pid is not null order by pid, nmol desc If you want the rows tie for max nmol within a pid then you can go to select aid,pid,nmol from atable where (pid,nmol) in (select pid, max(nmol) from atable where pid is not null group by pid) klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- 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] a SQL query question
Rajarshi Guha wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, I have a table of the form aid pid nmol - --- --- 123 34 245 3445 323 100 478 12 545 14 645 200 7null null In general, aid is unique, pid and nmol are non-unique. What I'm trying to do is to select those rows where pid is not null, grouped by pid. So I'd get the following aid pid nmol - --- --- 123 34 323 100 245 3445 545 14 645 200 478 12 From within each group I'd like to select the row that has the maximum value of nmol. So I'd end up with aid pid nmol - --- --- 323 100 245 3445 478 12 I can easily do the first step, but am struggling to make the SQL for the second step. Any pointers would be appreciated This should do it: SELECT DISTINCT ON (pid) aid, pid, nmol FROM foobar WHERE pid IS NOT NULL ORDER BY pid ASC, nmol DESC; The pid ASC satisfies the requirement for the DISTINCT ON part, while the nmol DESC ensures we get the MAX from each group. Or something like that. brian -- 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] why can't I load pgxml.sql
my version is 8.3.3: I found that it's part of the server. is it said that I should build postgresql with libxml again?? On Mon, 2008-07-28 at 23:00 -0400, Tom Lane wrote: Yi Zhao [EMAIL PROTECTED] writes: 1. ./configure --with-libxml --with-libxslt 2. makemake install (successful) 3. test=# \i /usr/local/pgsql/share/contrib/pgxml.sql error appearance: SET psql:/usr/local/pgsql/share/contrib/pgxml.sql:10: ERROR: could not load library /usr/local/pgsql/lib/pgxml.so: /usr/lib/libxml2.so.2: undefined symbol: gzopen64 Well, you've still failed to mention most all of the relevant information, like what platform this is and what PG version. But what the above suggests is that libxml2 is linked to a version of libz that doesn't appear anywhere in the dynamic linker's search path. How did you obtain or build libxml2, exactly? If this is a Linux platform, I think you might have missed out a ldconfig call or two. 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] a SQL query question
Hi, I have a table of the form aid pid nmol - --- --- 123 34 245 3445 323 100 478 12 545 14 645 200 7null null In general, aid is unique, pid and nmol are non-unique. What I'm trying to do is to select those rows where pid is not null, grouped by pid. So I'd get the following From within each group I'd like to select the row that has the maximum value of nmol. So I'd end up with aid pid nmol - --- --- 323 100 245 3445 478 12 I can easily do the first step, but am struggling to make the SQL for the second step. Any pointers would be appreciated Normally this is a difficult sort of thing to do, but it's made easier by a unique feature of Postgresql. Please try the following: SELECT DISTINCT ON (pid) aid, pid, nmol FROM tbl WHERE pid IS NOT NULL ORDER BY pid ASC, nmol DESC More information can be found here: http://www.postgresql.org/docs/8.3/interactive/sql-select.html#SQL-DISTINCT -- 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] a SQL query question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Jul 28, 2008, at 10:18 PM, Rajarshi Guha wrote: aid pid nmol - --- --- 323 100 245 3445 478 12 I can easily do the first step, but am struggling to make the SQL for the second step. Any pointers would be appreciated Thanks to the posters for helpful solutions - --- Rajarshi Guha [EMAIL PROTECTED] GPG Fingerprint: D070 5427 CC5B 7938 929C DD13 66A1 922C 51E7 9E84 - --- Alcohol, an alternative to your self - 'Alcohol' by the Bare Naked Ladies -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.8 (Darwin) iEYEARECAAYFAkiOnG8ACgkQZqGSLFHnnoR2qQCeMntkTpqR/ZaVS/nY1izO5u5y 0FYAn0dwi8v0jSB4OvK4OnwMr+7ypQPp =pNGY -END PGP SIGNATURE- -- 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] why can't I load pgxml.sql
Yi Zhao [EMAIL PROTECTED] writes:
my version is 8.3.3:
I kinda doubt that, actually, because you should not have been getting
function not found errors in 8.3. An 8.3 server built without XML
support should react more like this:
regression=# select xpath('foo','bar');
ERROR: unsupported XML feature
DETAIL: This functionality requires the server to be built with libxml support.
HINT: You need to rebuild PostgreSQL using --with-libxml.
In any case the error you're showing us suggests that the libxml
installation is broken, not PG itself.
You still haven't told us the platform...
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] Clone a database to other machine
you can use slony-i http://slony.info/ --- On Mon, 7/28/08, Garg, Manjit [EMAIL PROTECTED] wrote: From: Garg, Manjit [EMAIL PROTECTED] Subject: [GENERAL] Clone a database to other machine To: pgsql-general@postgresql.org Date: Monday, July 28, 2008, 4:44 PM Hi All, I'm stuck to an issue while cloning the pgsql database, can you please help, or give any docs to help out. Query - Trying to have same database on two seprate linux servers. One will be used to upport Applications and other will be used for Report generation only. Want to keep both the database in Sync, hourly or nightly. Kindly help to achive the same. Thanks and regards, Manjit Garg Corbus Global Support Team INDIA -- Email: [EMAIL PROTECTED] Phone: +91-120-304-4000, Ext 252 Fax : +91-120-256-7040 Mob : 9810679256 -- CONFIDENTIALITY NOTICE: This message, including any attachments hereto, (collectively the Email Message) is intended solely for the personal and confidential use of the designated recipient(s) and may contain privileged, proprietary, or otherwise private information which may be subject to attorney-client privilege or may constitute inside information protected by law. If the reader of this message is not the intended recipient, you are hereby notified of the following: (i) Any disclosure, printing, copying, or distribution of this Email Message by you or (ii) the taking of any action by you based on the contents of this Email Message or (iii) any other use of this Email Message by you, are strictly prohibited. If you have received this message in error, please notify the sender immediately and remove all traces of the electronic mail message and its attachments from your system. -- 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] why can't I load pgxml.sql
Yi Zhao wrote: my version is 8.3.3: I found that it's part of the server. is it said that I should build postgresql with libxml again?? googling gzopen64 throws up a whole lot of hits - most of them were debian/ubuntu and boiled down to some problem with versions of libz.so klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
