Re: [GENERAL] errors on restoring postgresql binary dump to glusterfs
On Mon, Apr 30, 2012 at 8:34 PM, Liang Ma wrote: > Hi There, > > While trying to restore a ~700GM binary dump by command > > pg_restore -d dbdata < sampledbdata-20120327.pgdump > > I encountered following errors repeatedly > > pg_restore: [archiver (db)] Error from TOC entry 2882463; 2613 > 10267347 BLOB 10267347 sdmcleod > pg_restore: [archiver (db)] could not execute query: ERROR: > unexpected data beyond EOF in block 500 of relation base/16386/11743 > HINT: This has been seen to occur with buggy kernels; consider > updating your system. Note the message right here... There may be further indications in the server log about what's wrong. > The server runs Ubuntu server 10.04 LTS with postgresql upgraded to > version 9.1.3-1~lucid. The postgresql data directory is located in a > glusterfs mounted directory to a replicated volume vol-2 I assume you don't have more than one node actually *accessing* the data directory at the same time, right? Even with that said, I haven't heard of anybody running PostgreSQL on glusterfs, and I'm not sure it fulfills the basic requirements that PostgreSQL has on a filesystem. In particular, the messages above about a buggy kernel certainly indicates that there is a problem with the filesystem. > I think this may have someting to do with glusterfs, because when I > restore the same dump to a same ubuntu 10.04 server with postgresql > upgraded to the same 9.1.3-1~lucid located in a local ext4 filesystem, > the pg_restore went well without a single error. Yes, it certainly sounds like that. You probably need to bring it up with the glusterfs folks... -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] spanish locale question
Tulio wrote: > Let me expand the collate situation. I´m from Perú and I have turned > everything in postgresql.conf as 'es_PE.UTF-8' even the > default_text_search_config = 'pg_catalog.spanish'. Even my Ubuntu 12.04 > works in English I have es_PE locale too. > if I do > > SELECT * FROM pru order by dad,mum,name; > > I get: > > id | dad | mum | name > +--+--+--- > 2 | leon | mendoza | juan > 6 | leon | valencia | josie > 5 | león | mendoza | jua > 3 | león | valárd | jose > 1 | león | valencia | josé > 7 | león | valencia | josie > 4 | león | válencia | jos > (7 rows) > > Which is a wrong order (collation) in Spanish and I don´t understand why. Maybe you misunderstood what it means to ORDER BY multiple columns. In your query, the rows are ordered by "dad", then all rows where "dad" is the same are ordered by "mum", and finally all rows where "dad" and "mum" are the same are ordered by "name". It is explained in the documentation: http://www.postgresql.org/docs/current/static/queries-order.html "When more than one expression is specified, the later values are used to sort rows that are equal according to the earlier values." > But, I noticed that if I do: > > SELECT * FROM pru order by dad || mum || name; > > I get the correct order: > > id | dad | mum | name > +--+--+--- > 5 | león | mendoza | jua > 2 | leon | mendoza | juan > 3 | león | valárd | jose > 4 | león | válencia | jos > 1 | león | valencia | josé > 6 | leon | valencia | josie > 7 | león | valencia | josie > (7 rows) > > > Is this the correct way to order in Postgresql and if it´s not Does anyone > have an idea and could please explain it to me? This is not PostgreSQL-specific behaviour, it is defined in the SQL standard and works like this on all database systems I know. You can use the ORDER BY clause you propose if you prefer this ordering. But would you really order 'leon', 'mendoza', 'juan' before 'leo', 'zara', 'juan'? Yours, Laurenz Albe -- 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] Is it possible to call other functions inside plpythonu?
On Fri, Apr 27, 2012 at 4:42 PM, Frank Lanitz wrote: > Hi folks, > > Just looking for a nice server side solution to implement some > fundamental logic for an application. plpythonu looks in this tmers very > well as I'm liking the syntax of Python. However, an very old blog post > at [1] made me unsure whether really to use it. Is it still (or has it > ever been) an issue that plpythonu is having a lot of overhead and not > able to make use of other functions? Didn't found anything on docu for > 9.1 about that. There is overhead transforming data to and from types that the Python interpreter can work with. How much overhead is rather subjective - we find it fast enough for the things we use it for. The Python syntax can become a bit cumbersome if you need to interact with the database a lot, in which case plpgSQL is likely the better language for that job. There are counter examples too, where the Python code is much cleaner and in some cases lets you do things impossible in plpgSQL. Access to the Python standard library gives you a great toolkit, and it being 'unsafe' you can do stuff you would otherwise need to write and deploy C extensions for. -- Stuart Bishop http://www.stuartbishop.net/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is it possible to call other functions inside plpythonu?
On Fri, May 4, 2012 at 4:09 PM, Stuart Bishop wrote:
> On Fri, Apr 27, 2012 at 4:42 PM, Frank Lanitz wrote:
>> Hi folks,
>>
>> Just looking for a nice server side solution to implement some
>> fundamental logic for an application. plpythonu looks in this tmers very
>> well as I'm liking the syntax of Python. However, an very old blog post
>> at [1] made me unsure whether really to use it. Is it still (or has it
>> ever been) an issue that plpythonu is having a lot of overhead and not
>> able to make use of other functions? Didn't found anything on docu for
>> 9.1 about that.
I forgot to mention that you can (and always have been able to) call
other functions using plpy.execute("SELECT some_func()"). If you need
to invoke Python functions, you can also just 'import' the module like
any other Python code and avoid the overheads of converting data too
and from PostgreSQL data types. You may need to ensure the PYTHONPATH
environment variable gets set correctly if you need to import your own
code from the filesystem rather than the standard library.
--
Stuart Bishop
http://www.stuartbishop.net/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to get the physical locations of tables, views, functions etc of Postgresql in Windows & Linux?
Hi Raghavendra,
Is it sure that we can copy only the data of Postgresql from one disk to other
seamlessly and then I can reuse the content without any hassle? If so,
tablespace is what I should create first?
Please clarify me on this.
Thanks & Regards,
Siva.
From: Raghavendra [mailto:raghavendra@enterprisedb.com]
Sent: Thursday, May 03, 2012 7:09 PM
To: Siva Palanisamy
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to get the physical locations of tables, views,
functions etc of Postgresql in Windows & Linux?
On Thu, May 3, 2012 at 6:52 PM, Siva Palanisamy
mailto:siv...@hcl.com>> wrote:
Hi there! I'm interested to get the physical locations of tables, views,
functions, data/content available in the tables of PostgreSQL in Linux OS. I've
a scenario that PostgreSQL could be installed in SD-Card facility and
Hard-Disk. If I've tables, views, functions, data in SD, I want to get the
physical locations of the same and merge/copy into my hard-disk whenever I wish
to replace the storage space. I hope the storage of database should be in terms
of plain files architecture.
You first step should start from $PGDATA/base/, you find OID's as directories
which are related to each database of your cluster. In OID's directory, you
find all the objects ID's for Tables/indexes/view etc.,
To know the object id, you can use a system defined function.
postgres=# select pg_relation_filepath('foo');
pg_relation_filepath
--
base/12780/16407
(1 row)
12780, is database OID.
For moving objects from one drive to other, you need to use tablespaces.
http://www.postgresql.org/docs/9.1/static/sql-createtablespace.html
Also, can I able to see the contents by opening its files? I mean, can I able
to access it? Please help me on this. Thanks!
You cannot know the contents in files, unless u are good hacker :)
---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/
::DISCLAIMER::
---
The contents of this e-mail and any attachment(s) are confidential and intended
for the named recipient(s) only.
E-mail transmission is not guaranteed to be secure or error-free as information
could be intercepted, corrupted,
lost, destroyed, arrive late or incomplete, or may contain viruses in
transmission. The e mail and its contents
(with or without referred errors) shall therefore not attach any liability on
the originator or HCL or its affiliates.
Views or opinions, if any, presented in this email are solely those of the
author and may not necessarily reflect the
views or opinions of HCL or its affiliates. Any form of reproduction,
dissemination, copying, disclosure, modification,
distribution and / or publication of this message without the prior written
consent of authorized representative of
HCL is strictly prohibited. If you have received this email in error please
delete it and notify the sender immediately.
Before opening any email and/or attachments, please check them for viruses and
other defects.
---
Re: [GENERAL] How to get the physical locations of tables, views, functions etc of Postgresql in Windows & Linux?
On Fri, May 4, 2012 at 3:31 PM, Siva Palanisamy wrote:
> Hi Raghavendra,
>
> ** **
>
> Is it sure that we can copy only the data of Postgresql from one disk to
> other seamlessly and then I can reuse the content without any hassle? If
> so, tablespace is what I should create first?
>
> Please clarify me on this.
>
> ** **
>
> Thanks & Regards,
>
> Siva.
>
> **
>
Yes, using tablespace, you can move object files across disks. Here is
small example, I have done on my local box but you can tweak as per your
tablespace locations.
First I place object in one tablespace and moved it another tablespace.
postgres=# create tablespace space1 location '/opt/PostgreSQL/9.1/tspace';
CREATE TABLESPACE
postgres=# create table foo(id int) tablespace space1;
CREATE TABLE
postgres=# \d foo
Table "public.foo"
Column | Type | Modifiers
+-+---
id | integer |
Tablespace: "space1"
postgres=# insert into foo select generate_series(1,1);
INSERT 0 1
postgres=# \dt+ foo
List of relations
Schema | Name | Type | Owner | Size | Description
+--+---+--++-
public | foo | table | postgres | 384 kB |
(1 row)
postgres=# select pg_tablespace_size('space1');
pg_tablespace_size
397312
(1 row)
Now create a new one and move it.
postgres=# create tablespace new_space location
'/opt/PostgreSQL/9.1/newspace';
CREATE TABLESPACE
postgres=# alter table foo set tablespace new_space;
ALTER TABLE
postgres=# \d foo
Table "public.foo"
Column | Type | Modifiers
+-+---
id | integer |
Tablespace: "new_space"
postgres=# select pg_tablespace_size('new_space');
pg_tablespace_size
397312
(1 row)
---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/
[GENERAL] Re: "OLD." || myColumnNameVar (How to generically access columns in a trigger's OLD or NEW records)
Hi everybody, I want to thank Basile for his function but I've problems with it. 1- When I tried to apply it in postgres SQL 8.4 on a DB in LATIN1 I've this message : ERROR: character 0xe280a2 of encoding "UTF8" has no equivalent in "LATIN1" 2- I tried this function on a DB in UTF8 and I've this message : ERROR: syntax error at or near "USING" LIGNE 34 : ...$1).' || ri.column_name || '::text' INTO newValue USING NEW; 3- So, I tried I delete the words USING NEW and USING and when I want to update or insert a data I've this message : there is no parameter $1 I don't understand how we can using the parameter $1 in an sql request. Thank you for your response and sorry for my English. Geo-x -- View this message in context: http://postgresql.1045698.n5.nabble.com/OLD-myColumnNameVar-How-to-generically-access-columns-in-a-trigger-s-OLD-or-NEW-records-tp4347470p5685049.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] Re: "OLD." || myColumnNameVar (How to generically access columns in a trigger's OLD or NEW records)
Hello this can help http://postgres.cz/wiki/PostgreSQL_SQL_Tricks#Iteration_over_RECORD_variable_inside_trigger Regards Pavel Stehule 2012/5/4 Geo-x : > Hi everybody, > > I want to thank Basile for his function but I've problems with it. > > 1- When I tried to apply it in postgres SQL 8.4 on a DB in LATIN1 I've this > message : > > ERROR: character 0xe280a2 of encoding "UTF8" has no equivalent in "LATIN1" > > 2- I tried this function on a DB in UTF8 and I've this message : > > ERROR: syntax error at or near "USING" > LIGNE 34 : ...$1).' || ri.column_name || '::text' INTO newValue USING NEW; > > 3- So, I tried I delete the words USING NEW and USING and when I want to > update or insert a data I've this message : > > there is no parameter $1 > > I don't understand how we can using the parameter $1 in an sql request. > > Thank you for your response and sorry for my English. > > Geo-x > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/OLD-myColumnNameVar-How-to-generically-access-columns-in-a-trigger-s-OLD-or-NEW-records-tp4347470p5685049.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
Re: [GENERAL] Re: "OLD." || myColumnNameVar (How to generically access columns in a trigger's OLD or NEW records)
On Fri, May 4, 2012 at 8:04 AM, Pavel Stehule wrote: > Hello > > this can help > http://postgres.cz/wiki/PostgreSQL_SQL_Tricks#Iteration_over_RECORD_variable_inside_trigger hstore has replaced information schema method for me when doing these type of things: see: http://archives.postgresql.org/pgsql-general/2012-02/msg00251.php merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Streaming replication: sequences on slave seemingly ahead of sequences on master
Hi list, we have two 9.1.2 servers on debian squeeze, and are setting up a simple streaming replication between the two. * wal_keep_segments is set high on the master * the slave's recovery.conf contains just standbay_mode=on and primary_conninfo=foo * we use a simple start_backup/rsync/stop_backup to create the base copy before starting the slave. It all seems to be working fine, except that when checking the data (selecting latest primary key and sequence value for all tables) on master and slave, some sequence ids are higher on the slave than on the master. I could understand if they were lower, but this is weird. * The slave's sequences can be anywhere between 1 and 50 ids ahead. * The actual table data is properly in sync. * We look at the slave before the master. * We ignore readings where pg_current_xlog_location() != pg_last_xlog_replay_location(). * It only happens on frequently-updated sequences. * During recovery, we have warnings of the form: 2012-05-04 10:32:08 CEST WARNING: xlog min recovery request 16A/2A03BDD0 is past current point 16A/1E72A880 2012-05-04 10:32:08 CEST CONTEXT: writing block 0 of relation base/35355/42224_vm xlog redo vacuum: rel 1663/1562168/1563037; blk 12122, lastBlockVacuumed 12070 2012-05-04 10:32:12 CEST WARNING: xlog min recovery request 16A/469F2120 is past current point 16A/1E9B6EB8 2012-05-04 10:32:12 CEST CONTEXT: writing block 0 of relation base/56308/57181_vm xlog redo vacuum: rel 1663/1562168/1563037; blk 21875, lastBlockVacuumed 21329 2012-05-04 10:32:17 CEST WARNING: xlog min recovery request 16A/22D497B8 is past current point 16A/1FF69258 * servers have near-identical hardware and software * monitoring via munin show at most 1-2 KB of replication lag * we retried the base backup twice So... * any likely mistake on our side ? * can it be fixed ? * is this harmless and to be ignored ? Thank you. -- Vincent de Phily -- 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] Streaming replication: sequences on slave seemingly ahead of sequences on master
On Fri, May 4, 2012 at 8:55 AM, Vincent de Phily wrote: > Hi list, > > we have two 9.1.2 servers on debian squeeze, and are setting up a simple > streaming replication between the two. > > * wal_keep_segments is set high on the master > * the slave's recovery.conf contains just standbay_mode=on and > primary_conninfo=foo > * we use a simple start_backup/rsync/stop_backup to create the base copy > before starting the slave. > > > It all seems to be working fine, except that when checking the data (selecting > latest primary key and sequence value for all tables) on master and slave, > some sequence ids are higher on the slave than on the master. I could > understand if they were lower, but this is weird. > > * The slave's sequences can be anywhere between 1 and 50 ids ahead. how did you determine that exactly? how do you know the transactions are committing in sequence order? merlin -- 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] Streaming replication: sequences on slave seemingly ahead of sequences on master
On Friday 04 May 2012 09:47:16 Merlin Moncure wrote: > On Fri, May 4, 2012 at 8:55 AM, Vincent de Phily > > wrote: > > Hi list, > > > > we have two 9.1.2 servers on debian squeeze, and are setting up a simple > > streaming replication between the two. > > > > * wal_keep_segments is set high on the master > > * the slave's recovery.conf contains just standbay_mode=on and > > primary_conninfo=foo > > * we use a simple start_backup/rsync/stop_backup to create the base copy > > before starting the slave. > > > > > > It all seems to be working fine, except that when checking the data > > (selecting latest primary key and sequence value for all tables) on > > master and slave, some sequence ids are higher on the slave than on the > > master. I could understand if they were lower, but this is weird. > > > > * The slave's sequences can be anywhere between 1 and 50 ids ahead. > > how did you determine that exactly? Quick and dirty : SQL=$(psql -tA -h $MASTER $DB <<< "select E'select \''||table_name||E'\', '|| column_name||' from '||table_name||' order by '||column_name||' desc limit 1;' from information_schema.columns where table_schema='public' and ordinal_position=1 order by table_name;select E'select \''||sequence_name|| E'\', last_value from '||sequence_name||';' from information_schema.sequences where sequence_schema='public' order by sequence_name;") psql -tA -h $SLAVE $DB <<< "select pg_last_xlog_replay_location();$SQL" > $SLAVE.check psql -tA -h $MASTER $DB <<< "select pg_current_xlog_location();$SQL" > $MASTER.check if diff -u $MASTER.check $SLAVE.check; then cat $MASTER.check echo -e "\e[32msync ok\e[m" else echo -e "\e[31msync bad\e[m" fi > how do you know the transactions > are committing in sequence order? I dont, actually. But whichever order the transactions eventually commit in, I'd expect that order to be the same on the slave and the host ? And I wouldn't expect anything to finish on the slave before it finishes on the master ? -- Vincent de Phily Mobile Devices +33 (0) 142 119 325 +353 (0) 85 710 6320 Warning This message (and any associated files) is intended only for the use of its intended recipient and may contain information that is confidential, subject to copyright or constitutes a trade secret. If you are not the intended recipient you are hereby notified that any dissemination, copying or distribution of this message, or files associated with this message, is strictly prohibited. If you have received this message in error, please notify us immediately by replying to the message and deleting it from your computer. Any views or opinions presented are solely those of the author vincent.deph...@mobile-devices.fr and do not necessarily represent those of the company. Although the company has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. -- 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] Streaming replication: sequences on slave seemingly ahead of sequences on master
This is due to how sequences are pre-allocated in blocks to sessions running on the master. Since the slave is updated via the WALs, and not via 'nextval' function calls in queries, the sequences that are actually used will remain in sync with the master. -- Mike Nolan
[GENERAL] .pgpass not working
Hi Apologies in advance if this is the wrong place to ask. I have Postres 8.4 and I am setting up replication with Slony. I'm having password issues with slony. I have roamed the net for a solution and the way to fix it is to use the .pgpass file or change pg_hba.conf to accept trusted connections. I do not want to touch the pg_hba.conf so I have generated the .pgpass file. The permissions is set to 600, and I have correctly inputted the details into .pgpass, there are no leading spaces. *myhostname:myport:*:postgres:mypassword* However I am still prompted for a password. I have tested pg_dump as well and it prompts also. Does anyone have any suggestions on what may be the culprit. Is there somewhere I need to specify to tell the system to look into the .pgpass file? many thanks rebecca
Re: [GENERAL] .pgpass not working
Rebecca Clarke writes: > I'm having password issues with slony. > I have roamed the net for a solution and the way to fix it is to use the > .pgpass file or change pg_hba.conf to accept trusted connections. > I do not want to touch the pg_hba.conf so I have generated the .pgpass file. Reasonable. > The permissions is set to 600, and I have correctly inputted the details > into .pgpass, there are no leading spaces. > *myhostname:myport:*:postgres:mypassword* > However I am still prompted for a password. > I have tested pg_dump as well and it prompts also. Hmm, you're not showing exactly how you're trying to connect, but it might be that you need "localhost" in the first field rather than the real machine name. If that's not it, I'd suggest you show us the actual file contents (you can blank out the password) and the actual pg_dump command you're trying. 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] spanish locale question
On Fri, May 4, 2012 at 1:22 AM, Albe Laurenz wrote: > But would you really order 'leon', 'mendoza', 'juan' before > 'leo', 'zara', 'juan'? No, I guess that Tulio would not do that, because (usually) 'leo' goes before 'leon'. What Tulio is saying is that 'leon' and 'león' are the same thing from the point of view of sorting in Spanish, but his PostgreSQL seems to think that 'leon' goes before 'león'. Al -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to know there is any ODBC Driver installed in the Linux redhat 5.7 server? Thanks
I tried test the ODBC driver. I failed and I was told I should install the odbc driver manager to configure the ODBC driver first, then I can test the ODBC drive. And the following ODBC driver were installed on my server : psqlodbc-08.02.0400 psqlodbc-08.03.0400 psqlodbc-09.00.0200 psqlodbc-08.02.0500 psqlodbc-08.04.0200 Please give me some help of the ODBC driver, Should I install the ODBC driver manager? something like data direct??? Thanks. Grace -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-know-there-is-any-ODBC-Driver-installed-in-the-Linux-redhat-5-7-server-Thanks-tp5686486.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] spanish locale question
Al Eridani writes: > What Tulio is saying is that 'leon' and 'león' are the same thing from > the point of view of sorting in Spanish, but his PostgreSQL seems to > think that 'leon' goes before 'león'. Postgres never considers that two distinct strings are "equal". If the locale setting considers these equal (which isn't entirely clear from the given evidence), PG would then sort them on the basis of their character code values. A possible workaround if you need to consider them equal is to strip the accents before sorting (ie, something like "ORDER BY to_ascii(col)") but this may well throw away more information than you want ... 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] .pgpass not working
On May 4, 2012, at 9:30 AM, Rebecca Clarke wrote: > I do not want to touch the pg_hba.conf so I have generated the .pgpass file. > The permissions is set to 600, and I have correctly inputted the details into > .pgpass, there are no leading spaces. > > myhostname:myport:*:postgres:mypassword > > However I am still prompted for a password. > I have tested pg_dump as well and it prompts also. > > Does anyone have any suggestions on what may be the culprit. Is there > somewhere I need to specify to tell the system to look into the .pgpass file? Where is the .pgpass file? If it's not in ~/.pgpass or doesn't have the right ownership (your permissions are good) then it won't be used. If it's in a different location, you might need to make use of the PGPASSFILE environment variable. If you really get stuck, you can always strace psql or pg_dump and see if it has problems opening your .pgpass file.
Re: [GENERAL] How to know there is any ODBC Driver installed in the Linux redhat 5.7 server? Thanks
On 05/04/2012 10:23 AM, leaf_yxj wrote: I tried test the ODBC driver. I failed and I was told I should install the odbc driver manager to configure the ODBC driver first, then I can test the ODBC drive. And the following ODBC driver were installed on my server : psqlodbc-08.02.0400 psqlodbc-08.03.0400 psqlodbc-09.00.0200 psqlodbc-08.02.0500 psqlodbc-08.04.0200 Please give me some help of the ODBC driver, Should I install the ODBC driver manager? something like data direct??? First, are you sure you want the Driver Manager on the Linux server not the Windows clients? Second if you are looking for Linux Driver Managers, the two I know of are: unixODBC http://www.unixodbc.org/ iODBC http://www.iodbc.org/dataspace/iodbc/wiki/iODBC/ Third there is a Postgres list dedicated to ODBC: http://archives.postgresql.org/pgsql-odbc/ Thanks. Grace -- Adrian Klaver adrian.kla...@gmail.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] set returning functions and resultset order
Hi all,
I'm a bit confused about when exactly I can rely on the resultset order
when using set returning functions. The documentation states:
'After a query has produced an output table (after the select list has been
processed) it can optionally be sorted. If sorting is not chosen, the rows
will be returned in an unspecified order.'
So when taking this very literally, I would expect that you cannot rely on
the order of
SELECT * FROM generate_series(2,4);
generate_series
-
2
3
4
I'm asking this because I'm writing queries similar to
SELECT string_agg(foo, '@') FROM regexp_split_to_table('1@2@3@4','@') foo;
string_agg
1@2@3@4
(1 row)
and
SELECT generate_subscripts(regexp_split_to_array('bird@dog@cow@ant','@'),
1), regexp_split_to_table('bird@dog@cow@ant','@');
generate_subscripts | regexp_split_to_table
-+---
1 | bird
2 | dog
3 | cow
4 | ant
(4 rows)
and need to know whether the order in these cases is guaranteed. Is there a
more general statement that could be made, something like:
If you use only set returning functions, and do not join their results,
then the returning order of the individual functions will be respected?
Thanks in advance,
Ingmar Brouns
Re: [GENERAL] Re: Move the postgreSQL database from Drive C to Map Network Drive (Called Z)
On 05/03/12 10:18 PM, David Johnston wrote: On May 3, 2012, at 20:20, EllyR wrote: > Yes you are right, I have read that it is not that reliable, but I have to do > something for that, what would be your suggestion for this case? Thanks. Simplest option: install a new local hard drive and configure a tablespace to use it. Alternative: Buy and configure a new machine then dump/restore your database to it. or, if your NAS/SAN supports it, configure an ISCSI device. except, I find ad-hoc ISCSI is not very reliable. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] spanish locale question
On 05/04/2012 07:31 PM, Tom Lane wrote:
Al Eridani writes:
What Tulio is saying is that 'leon' and 'león' are the same thing from
the point of view of sorting in Spanish, but his PostgreSQL seems to
think that 'leon' goes before 'león'.
Postgres never considers that two distinct strings are "equal". If the
locale setting considers these equal (which isn't entirely clear from
the given evidence), PG would then sort them on the basis of their
character code values.
A possible workaround if you need to consider them equal is to strip the
accents before sorting (ie, something like "ORDER BY to_ascii(col)") but
this may well throw away more information than you want ...
Note that to_ascii barfs on unicode-input:
ERROR: encoding conversion from UTF8 to ASCII not supported
Better install unaccent:
cd ./postgresql-9.1.2/contrib/unaccent
make install
psql
CREATE EXTENSION unaccent;
andreak=# select unaccent('león');
unaccent
--
leon
(1 row)
--
Andreas Joseph Krogh - mob: +47 909 56 963
Senior Software Developer / CEO - OfficeNet AS - http://www.officenet.no
Public key: http://home.officenet.no/~andreak/public_key.asc
--
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] set returning functions and resultset order
2012/5/4 Ingmar Brouns :
>
> Hi all,
>
> I'm a bit confused about when exactly I can rely on the resultset order when
> using set returning functions. The documentation states:
>
> 'After a query has produced an output table (after the select list has been
> processed) it can optionally be sorted. If sorting is not chosen, the rows
> will be returned in an unspecified order.'
>
> So when taking this very literally, I would expect that you cannot rely on
> the order of
>
> SELECT * FROM generate_series(2,4);
> generate_series
> -
> 2
> 3
> 4
>
>
> I'm asking this because I'm writing queries similar to
>
> SELECT string_agg(foo, '@') FROM regexp_split_to_table('1@2@3@4','@') foo;
> string_agg
>
> 1@2@3@4
> (1 row)
>
> and
>
> SELECT generate_subscripts(regexp_split_to_array('bird@dog@cow@ant','@'),
> 1), regexp_split_to_table('bird@dog@cow@ant','@');
> generate_subscripts | regexp_split_to_table
> -+---
> 1 | bird
> 2 | dog
> 3 | cow
> 4 | ant
> (4 rows)
>
> and need to know whether the order in these cases is guaranteed. Is there a
> more general statement that could be made, something like:
> If you use only set returning functions, and do not join their results, then
> the returning order of the individual functions will be respected?
result of SRF functions is ordered always - only when these processing
continues, then set can be reordered.
Regards
Pavel Stehule
>
> Thanks in advance,
>
> Ingmar Brouns
>
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
