Re: [GENERAL] client_encoding / postgresql strange behaviour
On Tue, Sep 16, 2008 at 05:15:41AM -0600, Enrico Sabbadin wrote: Hi, as far as I've understood client_encoding tells postgresql how data will arrive / must be send back to the client application. Postgresql will do the conversion for you if the database encoding is different. Correct. I've a unicode database and the line client_encoding=latin1 in the postgresql configuration file (this is the same as calling set client_encoding='latin1' on any connection right?). Correct. i insert one row containing the Hex 92 characther using the npgsql net driver : I sniff the wire and i see that that only the hex 92 bvbyte is sent. i insert one row containing the Hex 92 characther using the npgsql net driver setting Encoding=UNICODE in the cnstring: I sniff the wire and i see that that 3 bytes are sent : the coorect equivalent in unicode (i see that a set client_ecoeding=UNICODE is sent). That's wrong: if the original encoding is latin1 then 0x92 should have a 2-byte UTF-8 sequence of 0xc2 0x92, which represents U+0092 PRIVATE USE TWO, a C1 control character. I'd guess that the original encoding is really win1252 (aka Windows Latin 1), where 0x92 would have a 3-byte UTF-8 sequence of 0xe2 0x80 0x99, representing U+2019 RIGHT SINGLE QUOTATION MARK. Is that the character you're expecting? Now i want to read the data using : 1) if i read the first row keeping client_encoding latin1 everything is OK (i get hex 92 back) The database converted 0x92 to 0xc2 0x92 on the way in, then converted that character back to 0x92 on the way out. Your display is apparently interpreting this character the way you expect (presumably as U+2019 RIGHT SINGLE QUOTATION MARK) even though the database didn't interpret it that way. 2) if i read the 2nd row setting client_encoding unicode everything is OK (i get the 3 unicode bytes) Since you sent a 3-byte UTF-8 sequence the database is apparently storing the character you expect, presumably U+2019 RIGHT SINGLE QUOTATION MARK. 3) if i read the first row setting client_encoding unicode IT DOES NOT WORK I get a 2 byte sequence c2 92 ?? so i see garbage. The database stored the first row as 0xc2 0x92 U+0092 PRIVATE USE TWO because client_encoding was set to latin1. When you retrieved that character with client_encoding set to UNICODE (UTF8 in recent versions of Postgres) you got 0xc2 0x92 back. WHY this does not work (can't i write in one encoding and read in another?) Looks like postgresql did not correctly converted from latin1 to unicode and has gargabage inside (but why do i read correctly the first line when i have client_encoding latin1) ? Postgres probably did perform the correct conversion but your display is really something other than latin1, probably win1252 or another Windows encoding. Try setting client_encoding to win1252, which is supported in 8.1 and later. What version are you running? Since you refer to UNICODE (8.0 and earlier) instead of to UTF8 (8.1 and later) it's possible that you're running a version that doesn't support win1252. -- Michael Fuhr -- 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] Cluster Up-time.
On Mon, Aug 18, 2008 at 03:53:32AM -0700, Alexi Gen wrote: Is there a table/view available from where I can check what time the cluster was started? Need this to calculate the uptime of the cluster. In PostgreSQL 8.1 and later you can run these queries to get the start time and uptime: SELECT pg_postmaster_start_time(); SELECT now() - pg_postmaster_start_time(); -- Michael Fuhr -- 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] index speed and failed expectations?
On Mon, Aug 04, 2008 at 08:35:28AM -0500, Adam Rich wrote: This query from the console: select * from stats order by start_time; takes 8 seconds before starting its output. Am I wrong in assuming that the index on start_time should make ORDER BY orders of magnitude faster? Postgresql won't use the index for queries like this. Due to the MVCC implementation, the index does not contain all necessary information and would therefore be slower than using the table data alone. Not necessarily true. Despite the index not having enough information, the planner might still decide that using the index would be faster than executing a sort. create table stats ( id serial primary key, start_time timestamp with time zone not null ); insert into stats (start_time) select now() - random() * '1 year'::interval from generate_series(1, 10); create index stats_start_time_idx on stats (start_time); analyze stats; explain analyze select * from stats order by start_time; QUERY PLAN Index Scan using stats_start_time_idx on stats (cost=0.00..4767.83 rows=10 width=12) (actual time=0.146..994.674 rows=10 loops=1) Total runtime: 1419.943 ms (2 rows) set enable_indexscan to off; explain analyze select * from stats order by start_time; QUERY PLAN - Sort (cost=9845.82..10095.82 rows=10 width=12) (actual time=3240.976..3800.038 rows=10 loops=1) Sort Key: start_time - Seq Scan on stats (cost=0.00..1541.00 rows=10 width=12) (actual time=0.091..500.853 rows=10 loops=1) Total runtime: 4226.870 ms (4 rows) -- Michael Fuhr -- 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] Altering a column type w/o dropping views
On Mon, Jul 07, 2008 at 05:53:58PM +0800, Ow Mun Heng wrote: I'm going to alter a bunch a tables columns's data type and I'm being forced to drop a view which depends on the the colum. Why is that a problem? If you keep your object definitions in files (e.g., in a directory structure that's under revision control) then you can write a deployment script like the following (to be executed via psql): BEGIN; DROP VIEW view_name; ALTER TABLE table_name ALTER column_name TYPE type_name; \i views/view_name.sql COMMIT; -- Michael Fuhr -- 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] Unicode problem again
On Tue, Jun 24, 2008 at 09:16:37AM +0200, Albe Laurenz wrote: Garry Saddington wrote: ProgrammingError Error Value: ERROR: character 0xe28099 of encoding UTF8 has no equivalent in LATIN1 select distinct [...] This is UNICODE 0x2019, a right single quotation mark. This is a Windows character - the only non-UNICODE codepages I know that contain this character are the Microsoft codepages. [...] I have changed client_encoding to Latin1 to get over errors caused by having the database in UTF8 and users trying to enter special characters like £ signs. Unfortunately, it seems there are already UTF8 encodings in the DB that have no equivalent in Latin1 from before the change. Your input data seems to have a mix of encodings: sometimes you're getting pound signs in a non-UTF-8 encoding, but if characters like U+2019 RIGHT SINGLE QUOTATION MARK got into the database when client_encoding was set to UTF8 then at least some data must have been in UTF-8. If you're not certain that all data will be in the same encoding then you might need to attempt to detect the encoding and set client_encoding accordingly or convert the data to a common encoding in the application before inserting it (I've had to do this, sometimes on a line-by-line basis). Setting client_encoding has implications for display as well as for input: if the displaying application expects data in one encoding but you give it data in a different encoding then non-ASCII characters might not display correctly. -- Michael Fuhr -- 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] Unicode problem again
On Thu, Jun 26, 2008 at 03:31:01PM +0200, Albe Laurenz wrote: Michael Fuhr wrote: Your input data seems to have a mix of encodings: sometimes you're getting pound signs in a non-UTF-8 encoding, but if characters like U+2019 RIGHT SINGLE QUOTATION MARK got into the database when client_encoding was set to UTF8 then at least some data must have been in UTF-8. Sorry, but that's not true. That character is 0x9s in WINDOWS-1252. I think you mean 0x92. So it could have been that client_encoding was (correctly) set to WIN1252 and the quotation mark was entered as a single byte character. Yes, *if* client_encoding was set to win1252. However, in the following thread Garry said that he was getting encoding errors when entering the pound sign that were resolved by changing client_encoding (I suggested latin1, latin9, or win1252; he doesn't say which he used): http://archives.postgresql.org/pgsql-general/2008-06/msg00526.php If client_encoding had been set to win1252 then Garry wouldn't have gotten encoding errors when entering the pound sign because that character is 0xa3 in win1252 (also in latin1 and latin9). So either applications are setting client_encoding to different values, sometimes correctly and sometimes incorrectly (Garry, do you know if that could be happening?), or the data is sometimes in different encodings. If the data is being entered via a web application then the latter seems more likely, at least in my experience (I've had to deal with exactly this problem recently). -- Michael Fuhr -- 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] test aggregate functions without a dummy table
On Fri, Jun 20, 2008 at 10:11:08AM -0400, Tom Lane wrote: Willy-Bas Loos [EMAIL PROTECTED] writes: I want to test the behavior of an an aggregate without creating a dummy table for it. But the code for it is horrible. Is there no simpler way? select max(foo) from (select 1 as foo union select 2 as foo)bar; Perhaps VALUES? regression=# select max(foo) from (values(1,2),(3,4),(5,6)) as v(foo,bar); Or perhaps using a set-returning function like generate_series(): test= select max(foo) from generate_series(1, 100) as g(foo); max - 100 (1 row) -- Michael Fuhr -- 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] Vacuum and inserts
On Wed, Jun 18, 2008 at 12:29:46PM -0700, Mark Wilden wrote: My coworker and I are having an argument about whether it's necessary to VACUUM an insert-only table. My theory is that since there are no outdated nor deleted rows, VACUUM doesn't do anything. Rolled back transactions on an insert-only table can leave behind dead rows. Also, even if the table never has dead rows you still have to vacuum periodically to prevent transaction ID wraparound. http://www.postgresql.org/docs/8.3/interactive/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND To avoid this, it is necessary to vacuum every table in every database at least once every two billion transactions. -- Michael Fuhr -- 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] UTF8 encoding problem
On Wed, Jun 18, 2008 at 08:25:07AM +0200, Giorgio Valoti wrote: On 18/giu/08, at 03:04, Michael Fuhr wrote: Is the data UTF-8? If the error is 'invalid byte sequence for encoding UTF8: 0xa3' then you probably need to set client_encoding to latin1, latin9, or win1252. Why? UTF-8 has rules about what byte values can occur in sequence; violations of those rules mean that the data isn't valid UTF-8. This particular error says that the database received a byte with the value 0xa3 (163) in a sequence of bytes that wasn't valid UTF-8. The UTF-8 byte sequence for the pound sign (£) is 0xc2 0xa3. If Garry got this error (I don't know if he did; I was asking) then the byte 0xa3 must have appeared in some other sequence that wasn't valid UTF-8. The usual reason for that is that the data is in some encoding other than UTF-8. Common encodings for Western European languages are Latin-1 (ISO-8859-1), Latin-9 (ISO-8859-15), and Windows-1252. All three of these encodings use a lone 0xa3 to represent the pound sign. If the data has a pound sign as 0xa3 and the database complains that it isn't part of a valid UTF-8 sequence then the data is likely to be in one of these other encodings. -- Michael Fuhr -- 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] UTF8 encoding problem
On Tue, Jun 17, 2008 at 10:48:34PM +0100, Garry Saddington wrote: I am getting illegal UTF8 encoding errors and I have traced it to the £ sign. What's the exact error message? I have set lc_monetary to lc_monetary = 'en_GB.UTF-8' in postgresql.conf but this has no effect. How can I sort this problem? Client_encoding =UTF8. Is the data UTF-8? If the error is 'invalid byte sequence for encoding UTF8: 0xa3' then you probably need to set client_encoding to latin1, latin9, or win1252. -- Michael Fuhr -- 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 sequential scan is used on indexed column ???
On Sat, Jun 14, 2008 at 02:35:38PM -0400, Tom Lane wrote: Michael Fuhr [EMAIL PROTECTED] writes: I created a test case that has close to the same estimated and actual row counts and has the same plan if I disable enable_nestloop: There's something weird about this --- why does the second plan seqscan b_saskaita, instead of using the bitmap scan that it had previously estimated to be cheaper? Dunno. What PG version are you testing, and can you provide the full test case? My test was in 8.2.9, the only version I had handy at the time. I later tested 8.1.13 (Julius said he was running 8.1.4) and got the same plan that Julius got without messing with planner settings. I don't have access to my test case right now but I'll post it when I get a chance. I simply populated the tables with random data, adjusting the amount and distribution until I got row count estimates close to what Julius got. I don't know if my test case is close enough to Julius's data to be relevant to his problem but if you think my results are weird then maybe I've stumbled across something else that's interesting. (As for the original question, the hash plan seems to me to be perfectly reasonable for the estimated row counts --- fetching one row out of fifty using an indexscan is going to be expensive. So I think the OP's problem is purely a statistical one, or maybe he's in a situation where he should reduce random_page_cost.) Hmmm...8.1.13 wants to do the hash join that you think would be reasonable but 8.2.9 prefers the nested loop as in my second example. I think I did have a reduced random_page_cost (2 as I recall). -- Michael Fuhr -- 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 sequential scan is used on indexed column ???
On Sat, Jun 14, 2008 at 04:59:44PM +0200, Andreas Kretschmer wrote: Julius Tuskenis [EMAIL PROTECTED] schrieb: I have a question concerning performance. One of my queries take a long to execute. I tried to do explain analyse and I see that the sequential scan is being used, although I have indexes set on columns that are used in joins. The question is - WHY, and how to change that behavior??? Try to create an index on apsilankymai.sas_id In the DDL that Julius posted apsilankymai doesn't have an sas_id column. The join is on apsilankymai.aps_saskaita = b_saskaita.sas_id. Both columns have an index: b_saskaita.sas_id is a primary key so it should have an index implicitly, and apsilankymai.aps_saskaita has an explicit CREATE INDEX statement. The WHERE clause is on b_saskaita.sas_subjektas, which also has an explicit CREATE INDEX statement. Unless I'm mistaken all relevant columns have an index. A few of the row count estimates differ from reality: Hash Join (cost=5.17..10185.89 rows=6047 width=138) (actual time=10698.539..10698.539 rows=0 loops=1) Bitmap Heap Scan on b_saskaita (cost=2.03..5.14 rows=9 width=96) (actual time=31.473..31.489 rows=1 loops=1) However, that might not be entirely responsible for the questionable plan. I created a test case that has close to the same estimated and actual row counts and has the same plan if I disable enable_nestloop: set enable_nestloop to off; explain analyze select * FROM b_saskaita JOIN apsilankymai ON (aps_saskaita = sas_id) where sas_subjektas = 20190; QUERY PLAN -- Hash Join (cost=6.54..5814.42 rows=5406 width=286) (actual time=3222.429..3222.429 rows=0 loops=1) Hash Cond: (apsilankymai.aps_saskaita = b_saskaita.sas_id) - Seq Scan on apsilankymai (cost=0.00..4627.50 rows=300350 width=42) (actual time=0.085..1514.863 rows=300350 loops=1) - Hash (cost=6.43..6.43 rows=9 width=244) (actual time=0.122..0.122 rows=1 loops=1) - Bitmap Heap Scan on b_saskaita (cost=2.32..6.43 rows=9 width=244) (actual time=0.089..0.095 rows=1 loops=1) Recheck Cond: (sas_subjektas = 20190) - Bitmap Index Scan on fki_sas_subjektas (cost=0.00..2.32 rows=9 width=0) (actual time=0.066..0.066 rows=1 loops=1) Index Cond: (sas_subjektas = 20190) Total runtime: 3222.786 ms I get a better plan if I enable nested loops: set enable_nestloop to on; explain analyze select * FROM b_saskaita JOIN apsilankymai ON (aps_saskaita = sas_id) where sas_subjektas = 20190; QUERY PLAN --- Nested Loop (cost=79.93..4660.23 rows=5406 width=286) (actual time=1.000..1.000 rows=0 loops=1) - Seq Scan on b_saskaita (cost=0.00..10.25 rows=9 width=244) (actual time=0.116..0.870 rows=1 loops=1) Filter: (sas_subjektas = 20190) - Bitmap Heap Scan on apsilankymai (cost=79.93..441.58 rows=6007 width=42) (actual time=0.084..0.084 rows=0 loops=1) Recheck Cond: (apsilankymai.aps_saskaita = b_saskaita.sas_id) - Bitmap Index Scan on idx_aps_saskaita (cost=0.00..78.43 rows=6007 width=0) (actual time=0.068..0.068 rows=0 loops=1) Index Cond: (apsilankymai.aps_saskaita = b_saskaita.sas_id) Total runtime: 1.321 ms Julius, do you perchance have enable_nestloop = off? If so, do you get a better plan if you enable it? Also, have you run ANALYZE lately? -- Michael Fuhr -- 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] REGEXP_REPLACE woes
On Tue, Jun 10, 2008 at 01:28:06PM +0200, Leif B. Kristensen wrote:
I want to transform the text '[p=1242|John Smith]' to
a href=./family.php?person=1242John Smith/a, but what I get is:
pgslekt= select REGEXP_REPLACE('[p=1242|John Smith]',
pgslekt( E'[p=(\d+)|(.+?)]',
pgslekt( E'a href=./family.php?person=\\1\\2/a');
regexp_replace
--
[a href=./family.php?person=/a=1242|John Smith]
(1 row)
What am I doing wrong?
Parts of the regular expression need more escaping. Try this:
select regexp_replace(
'[p=1242|John Smith]',
e'\\[p=(\\d+)\\|(.+?)\\]',
e'a href=./family.php?person=\\1\\2/a'
);
regexp_replace
---
a href=./family.php?person=1242John Smith/a
Caution: this method doesn't do HTML entity escaping so if your
input isn't trustworthy then you could end up with HTML that's
different from what you intended.
--
Michael Fuhr
--
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] REGEXP_REPLACE woes
On Tue, Jun 10, 2008 at 02:25:44PM +0200, Leif B. Kristensen wrote: Thank you Michael, I figured it was something fishy with the escaping. When I try your example, I get pgslekt= select regexp_replace( pgslekt( '[p=1242|John Smith]', pgslekt( e'\\[p=(\\d+)\\|(.+?)\\]', pgslekt( e'a href=./family.php?person=\\1\\2/a' pgslekt( ); ERROR: syntax error at or near LINE 2: '[p=1242|John Smith]', Something between my message and your shell appears to have converted a few spaces to no-break spaces. A hex dump of your query shows the following: 73 65 6c 65 63 74 20 72 65 67 65 78 70 5f 72 65 |select regexp_re| 0010 70 6c 61 63 65 28 0a c2 a0 20 c2 a0 27 5b 70 3d |place(. '[p=| Notice the byte sequences c2 a0, which is the UTF-8 encoding of U+00A0 NO-BREAK SPACE. Apparently psql doesn't like that. I don't see that sequence in my original message: 73 65 6c 65 63 74 20 72 65 67 65 78 70 5f 72 65 |select regexp_re| 0010 70 6c 61 63 65 28 0a 20 20 20 27 5b 70 3d 31 32 |place(. '[p=12| -- Michael Fuhr -- 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] REGEXP_REPLACE woes
On Tue, Jun 10, 2008 at 02:59:53PM +0200, Leif B. Kristensen wrote:
So far, so good. But look here:
pgslekt= select link_expand('[p=123|John Smith] and [p=456|Jane Doe]');
link_expand
---
a href=./family.php?person=123John Smith] and [p=456|Jane Doe/a
(1 row)
Hey, I told it not to be greedy, didn't I?
Yes, but regexp_replace only replaces that part of the original
string that matches the regular expression -- the rest it leaves
alone.
--
Michael Fuhr
--
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] REGEXP_REPLACE woes
On Tue, Jun 10, 2008 at 07:41:53AM -0600, Michael Fuhr wrote:
On Tue, Jun 10, 2008 at 02:59:53PM +0200, Leif B. Kristensen wrote:
So far, so good. But look here:
pgslekt= select link_expand('[p=123|John Smith] and [p=456|Jane Doe]');
link_expand
---
a href=./family.php?person=123John Smith] and [p=456|Jane Doe/a
(1 row)
Hey, I told it not to be greedy, didn't I?
Yes, but regexp_replace only replaces that part of the original
string that matches the regular expression -- the rest it leaves
alone.
Sorry, this isn't quite right. As you already discovered, the
pattern was being more greedy than you wanted. That's one reason
why I often use an inverted class instead of assuming that a
non-greedy quantifier will grab only what I want.
select regexp_replace(
'[p=123|John Smith] and [p=456|Jane Doe]',
E'\\[p=(\\d+)\\|([^]]+)\\]',
E'a href=./family.php?person=\\1\\2/a',
'g'
);
regexp_replace
-
a href=./family.php?person=123John Smith/a and a
href=./family.php?person=456Jane Doe/a
--
Michael Fuhr
--
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] Postgres Encoding conversion problem
On Tue, Apr 22, 2008 at 10:37:59AM +0200, Albe Laurenz wrote: Clemens Schwaighofer wrote: I sometimes have a problem with conversion of encodings eg from UTF-8 tio ShiftJIS: ERROR: character 0xf0a0aeb7 of encoding UTF8 has no equivalent in SJIS I have no idea what character this is, I cannot view it in my browser, etc. It translates to Unicode 10BB7, which is not defined. Actually it's U+20BB7 CJK UNIFIED IDEOGRAPH-20BB7. http://www.unicode.org/cgi-bin/GetUnihanData.pl?codepoint=20BB7 -- Michael Fuhr -- 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] table of US states' neighbours
On Thu, Mar 27, 2008 at 06:00:57PM -0400, Colin Wetherbee wrote:
brian wrote:
I'd like to add a table, state_neighbours, which joins each state with all
of its neighbour states. Does anyone know where I can find such a list?
I'm not interested in nearest neighbour, just any connected state.
That sounds like something you could create in 20 minutes with a map.
Or a few minutes with shapefiles and PostGIS, using the latter's
spatial functions to identify geometries that touch. Below are the
results of such an operation; I haven't verified the entire list
but I did check a few and they were correct.
AL|{FL,GA,MS,TN}
AR|{LA,MO,MS,OK,TN,TX}
AZ|{CA,CO,NM,NV,UT}
CA|{AZ,NV,OR}
CO|{AZ,KS,NE,NM,OK,UT,WY}
CT|{MA,NY,RI}
DC|{MD,VA}
DE|{MD,NJ,PA}
FL|{AL,GA}
GA|{AL,FL,NC,SC,TN}
IA|{IL,MN,MO,NE,SD,WI}
ID|{MT,NV,OR,UT,WA,WY}
IL|{IA,IN,KY,MI,MO,WI}
IN|{IL,KY,MI,OH}
KS|{CO,MO,NE,OK}
KY|{IL,IN,MO,OH,TN,VA,WV}
LA|{AR,MS,TX}
MA|{CT,NH,NY,RI,VT}
MD|{DC,DE,PA,VA,WV}
ME|{NH}
MI|{IL,IN,MN,OH,WI}
MN|{IA,MI,ND,SD,WI}
MO|{AR,IA,IL,KS,KY,NE,OK,TN}
MS|{AL,AR,LA,TN}
MT|{ID,ND,SD,WY}
NC|{GA,SC,TN,VA}
ND|{MN,MT,SD}
NE|{CO,IA,KS,MO,SD,WY}
NH|{MA,ME,VT}
NJ|{DE,NY,PA}
NM|{AZ,CO,OK,TX,UT}
NV|{AZ,CA,ID,OR,UT}
NY|{CT,MA,NJ,PA,VT}
OH|{IN,KY,MI,PA,WV}
OK|{AR,CO,KS,MO,NM,TX}
OR|{CA,ID,NV,WA}
PA|{DE,MD,NJ,NY,OH,WV}
RI|{CT,MA}
SC|{GA,NC}
SD|{IA,MN,MT,ND,NE,WY}
TN|{AL,AR,GA,KY,MO,MS,NC,VA}
TX|{AR,LA,NM,OK}
UT|{AZ,CO,ID,NM,NV,WY}
VA|{DC,KY,MD,NC,TN,WV}
VT|{MA,NH,NY}
WA|{ID,OR}
WI|{IA,IL,MI,MN}
WV|{KY,MD,OH,PA,VA}
WY|{CO,ID,MT,NE,SD,UT}
--
Michael Fuhr
--
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] beginner postgis question lat/lon
On Wed, Feb 27, 2008 at 04:59:07PM -0800, shadrack wrote:
This may seem like a very simple question...it is...but I can't find
documentation on it to help. I've seen some posts about lat/long but
none that give simple solutions on how to insert lat/long in tables.
See the PostGIS documentation, in particular Chapter 4 Using PostGIS:
http://postgis.refractions.net/docs/ch04.html
(The site isn't responding right now; hopefully it'll be available
soon.)
postgis=# insert into routes_geom values(1, 'J084',
GeomFromText('LINESTRING(38.20 -121.00, 38.20, -118.00)', 4326));
I receive this error:
ERROR: parse error - invalid geometry
CONTEXT: SQL function geomfromtext statement 1
There are two problems with the geometry string: the syntax error is
due an extra comma in the second pair of coordinates, and coordinates
should be (X Y) therefore (lon lat) instead of (lat lon). Try this:
insert into routes_geom values(1, 'J084', GeomFromText('LINESTRING(-121.00
38.20, -118.00 38.20)', 4326));
You might wish to subscribe to the postgis-users mailing list if you
have additional questions.
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] what happens if something goes wrong in transaction?
On Thu, Feb 28, 2008 at 02:19:01PM +, A B wrote: Hi. newbie question, but what will happen if I do begin work; select ... insert ... and so on... commit and somewhere a query fails. will I get an automatic rollback? After the error every subsequent statement will fail with ERROR: current transaction is aborted, commands ignored until end of transaction block. The transaction doesn't automatically end but it will roll back even if you try to commit (assuming you didn't do a partial rollback with SAVEPOINT/ROLLBACK TO). I'm using php to make all these calls and they have all to be succesfull or no one of them should be carried out. That's the behavior you'll get if you use a transaction. No changes will be visible to other transactions until you successfully commit. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Order of SUBSTR and UPPER in statement
[Please copy the mailing list on replies so others can contribute to and learn from the discussion.] On Thu, Feb 14, 2008 at 09:56:36AM +0100, Hermann Muster wrote: The statement I'm using is the following: SELECT FIRMEN.Firma,FIRMEN.Firma2,FIRMEN.Firma3,FIRMEN.Such,FIRMEN.Land,FIRMEN.PLZZ,FIRMEN.Ort,FIRMEN.Strasse,FIRMEN.PLZP,FIRMEN.Postfach,FIRMEN.Telefon,FIRMEN.Telefax,FIRMEN.eMail,FIRMEN.Internet,FIRMEN.KundenNr,FIRMEN.UST_ID,FIRMEN.ABC,FIRMEN.Zusatz1,FIRMEN.Zusatz2,FIRMEN.Zusatz3,FIRMEN.Zusatz4,FIRMEN.Zusatz5,FIRMEN.BLZ,FIRMEN.KtoNr,FIRMEN.Bank,FIRMEN.IBAN,FIRMEN.Kreditkart,FIRMEN.KreditkNr,FIRMEN.AdressTyp,FIRMEN.VKGebiet,FIRMEN.Zahlungart,FIRMEN.UmsatzSoll,FIRMEN.BonAnfrDat,FIRMEN.BonInfoDat,FIRMEN.BonIndex,FIRMEN.BonLimit,FIRMEN.BonOK,FIRMEN.BonInfo,FIRMEN.BonKapital,FIRMEN.BonUmsJahr,FIRMEN.BonMAZahl,FIRMEN.BonZahlung,FIRMEN.Betreuer,FIRMEN.Com_Wahl,FIRMEN.Symbol,FIRMEN.ErfDat,FIRMEN.ErfUser,FIRMEN.L_Dat,FIRMEN.L_User,FIRMEN.RecordID,FIRMEN.Z_Import_PK,FIRMEN.Z_Import_FK,FIRMEN.KreditkInh,FIRMEN.Br anchenTyp1,FIRMEN.BranchenTyp2,FIRMEN.KK_Exp_J,FIRMEN.KK_Exp_M,FIRMEN.Kategorie FROM FIRMEN WHERE FIRMEN.RecordID IN (SELECT DISTINCT X.RecordID FROM FIRMEN X INNER JOIN FIRMEN Y ON COALESCE(UPPER(SUBSTR(X.Firma,1,7)) = I haven't examined the entire query but the above line appears to be the problem. Did you mean to write the following? COALESCE(UPPER(SUBSTR(X.Firma,1,7)),'') = COALESCE(UPPER(SUBSTR(Y.Firma,1,7)),'') AND COALESCE(UPPER(X.PLZZ),'') = COALESCE(UPPER(Y.PLZZ),'') AND COALESCE(UPPER(X.PLZP),'') = COALESCE(UPPER(Y.PLZP),'') AND X.RecordID Y.RecordID) -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Order of SUBSTR and UPPER in statement
On Thu, Feb 14, 2008 at 04:48:33PM +0100, Hermann Muster wrote: Michael Fuhr schrieb: COALESCE(UPPER(SUBSTR(X.Firma,1,7)) = I haven't examined the entire query but the above line appears to be the problem. Did you mean to write the following? COALESCE(UPPER(SUBSTR(X.Firma,1,7)),'') = Yes, that's what I wanted to write. However, it only works when I change the order of UPPER and SUBSTR in the statement. I still don't believe that order of UPPER and SUBSTR is relevant to the syntax error. Please post two complete queries, one with the order that works and one with the order that doesn't. -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Order of SUBSTR and UPPER in statement
On Wed, Feb 13, 2008 at 04:19:09PM +0100, Hermann Muster wrote:
I encountered something I can't really explain. I use the following
statement in my application:
COALESCE(UPPER(SUBSTR(Y.Firma,1,7)),'')
This returns ERROR: syntax error at end of input
Please show a complete statement and not just a portion of it. This
expression works for me:
test= CREATE TABLE Y (Firma varchar);
CREATE TABLE
test= INSERT INTO Y (Firma) VALUES ('abcdefghij');
INSERT 0 1
test= SELECT COALESCE(UPPER(SUBSTR(Y.Firma,1,7)),'') FROM Y;
coalesce
--
ABCDEFG
(1 row)
However, using the following statement is fine:
COALESCE(SUBSTR(UPPER(X.Firma), 1, 7), '')
The fieldtype of Firma is character varying.
The only difference is the order of UPPER and SUBSTR.
I doubt that; I suspect the query that's failing has some other
problem that's causing the syntax error. Take a closer look,
especially at the end of the query string (syntax error at end of
input).
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Change column type to numeric
On Sun, Feb 10, 2008 at 11:37:45AM -0700, Jake Franklin wrote: test=# alter table foo alter column amount type numeric(10,2) USING cast(amount AS numeric); ERROR: invalid input syntax for type numeric: I'm assuming that it's trying to cast a blank value as numeric and failing. Does anyone know of an easy way to work around this? You could convert the empty strings to NULL: USING cast(nullif(amount, '') AS numeric) -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Analyze Explanation
On Tue, Jan 29, 2008 at 10:11:38AM -0800, Willem Buitendyk wrote: When I run Analyze I get the following notice repeated for many of my tables: NOTICE: no notnull values, invalid stats Is this just refering to my empty tables? The PostGIS function compute_geometry_stats() logs such a message. I'd guess you're using PostGIS and those tables have NULL in all rows' geometry columns. -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] looking for some real world performance numbers
On Sat, Oct 20, 2007 at 11:11:32PM -0700, snacktime wrote: So what would really help me is some real world numbers on how postgresql is doing in the wild under pressure. If anyone cares to throw some out I would really appreciate it. One of my databases has about 70M rows inserted, 30M rows updated, 70M rows deleted, and 3G rows retrieved per day. At peak times of the day it sustains around 120K rows/minute inserted, 80K rows/minute updated or deleted, and 3.5M rows/minute retrieved and it has room to grow. Usage patterns are different than for a web application, however: transaction rates are low (a few hundred per minute) and most logic is in database functions with statements that operate on hundreds or thousands of rows at a time. Still, this gives an idea of what a PostgreSQL database on decent hardware can handle. -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] looking for some real world performance numbers
On Mon, Oct 22, 2007 at 01:33:54PM +0200, vincent wrote: One of my databases has about 70M rows inserted, 30M rows updated, 70M rows deleted, and 3G rows retrieved per day. At peak times of the day it sustains around 120K rows/minute inserted, 80K rows/minute updated or deleted, and 3.5M rows/minute retrieved and it has room to grow. Usage patterns are different than for a web application, however: transaction rates are low (a few hundred per minute) and most logic is in database functions with statements that operate on hundreds or thousands of rows at a time. Still, this gives an idea of what a PostgreSQL database on decent hardware can handle. What kind of hardware are you using for this system? Just to get an idea of what 'decent hardware' is in this case. I don't know the full specs because another group is responsible for that. I think the box has four Athlon 64 X2s with 32G RAM. At least some of the storage is SAN-attached. -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Please change default characterset for database cluster
On Fri, Sep 28, 2007 at 09:32:43PM -0400, Carlos Moreno wrote: Oh, and BTW, welcome to version 8 of PostgreSQL ... The default encoding for initdb is . Ta-d!!! Unicode !!! No, it isn't. If you get UTF8 (formerly UNICODE) as a default then it's because initdb is picking it up from your environment. http://www.postgresql.org/docs/8.2/interactive/app-initdb.html The default is derived from the locale, or SQL_ASCII if that does not work. -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [Urgent] Regexp_replace question
On Tue, Sep 25, 2007 at 01:36:26PM +0800, Phoenix Kiula wrote: How can I remove characters that form a part of regular expressions? Why do you want to do that? I would like to remove all instances of the following characters: [ ] \ + test= select id, t, regexp_replace(t, e'[[\\]+]', '', 'g') from foo; id | t | regexp_replace ++ 1 | foo[]+\bar | foobar (1 row) test= select id, t, translate(t, e'[]\\+', '') from foo; id | t | translate ++--- 1 | foo[]+\bar | foobar (1 row) -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] UNIQUE_VIOLATION exception, finding out which index would have been violated
On Tue, Sep 25, 2007 at 04:55:37AM -0200, Petri Simolin wrote: I have created a function which inserts a row in a table which has 2 unique indexes on two different columns. I am wondering, if there is a way in case of UNIQUE_VIOLATION exception to find out which index would have been violated? In PL/pgSQL you could extract the constraint name from SQLERRM, which should be a string like 'duplicate key violates unique constraint foo_id1_key'. -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] strange TIME behaviour
On Sat, Sep 15, 2007 at 04:45:02PM +0500, rihad wrote: Can someone please explain to me why these two give different results? The idea is to get the number of seconds past 00:00:00, so the second one is obviously correct. They're both correct. foo= select extract(epoch from current_time); date_part -- 42023.026348 (1 row) current_time is a time with time zone; the above query returns the number of seconds since 00:00:00 UTC. foo= select extract(epoch from cast(current_time as time)); date_part -- 60030.824587 (1 row) By casting current_time to time without time zone you're now getting the number of seconds since 00:00:00 in your local time zone. -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Selecting rows where timestamp between two timestamps
On Mon, Aug 13, 2007 at 07:16:30AM -0700, Jeff Lanzarotta wrote: select * from foobar where ts between now() and now() - interval '5 days' btw, the column ts is defined as: ts timestamp with time zone NOT NULL DEFAULT now() No rows are returned, but I know there are at least 100 rows that should be returned... Put the lower value first or use BETWEEN SYMMETRIC: select * from foobar where ts between now() - interval '5 days' and now() select * from foobar where ts between symmetric now() and now() - interval '5 days' -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: index bloat WAS: [GENERAL] reindexing pg_shdepend
On Thu, Aug 02, 2007 at 10:40:24PM -0400, Joseph S wrote: Tom Lane wrote: Heavy use of temp tables would expand pg_class, pg_type, and especially pg_attribute, but as long as you have a decent vacuuming regimen (do you use autovac?) they shouldn't get out of hand. I do use autovac. Like I said they don't get really out of hand, only up to 20 megs or so before I noticed that it was weird. The large indexes are what tipped me off that something strange was going on. Unexpected bloat in pg_shdepend led me to discover a problem with statistics for shared tables a couple of months ago: http://archives.postgresql.org/pgsql-hackers/2007-06/msg00190.php http://archives.postgresql.org/pgsql-hackers/2007-06/msg00245.php I only noticed this because I was making an effort to monitor index bloat on my regular tables. It could be there are a lot of people out there who are experiencing this but don't notice because 20 megs here and there don't cause any noticeable problems. Anybody making heavy use of temporary tables and relying on autovacuum is probably suffering bloat in pg_shdepend because no released version of PostgreSQL has the fix for the statistics bug (it has been fixed in CVS, however). As I mention in the second message above, vacuuming pg_shdepend resulted in an immediate performance improvement in an application I was investigating. -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] pg_dump
On Mon, Jul 30, 2007 at 07:26:45PM -0400, Tom Allison wrote: pg_dump does not support SSL connections? pg_dump sits atop libpq, which can use SSL if PostgreSQL was built with SSL support. I have been using pgsql with ssl connections to my database. But when I tried pg_dump I was hit with the no ssl error message. What was the exact command and the exact error message? Didn't see an option for it in the RTFM so .. Am I correct in assuming that pg_dump/pg_restore are not supposed to run via ssl? No, that's not correct; pg_dump can use SSL just as any other libpq application can. Are you sure your pg_dump is linked against an SSL-enabled libpq? Have you tried setting the PGSSLMODE environment variable? What version of PostgreSQL are you running? -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Require entry of MD5 hash instead of plaintext password?
On Fri, Jul 27, 2007 at 09:33:37AM -0700, [EMAIL PROTECTED] wrote: Is there a way to configure PostgreSQL 8.0 so that when prompted for a password, the user enters the MD5 hash of his password, instead of the normal plaintext password? What problem are you trying to solve? -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Backslah in encrypt function.
On Wed, Jul 25, 2007 at 06:02:10PM +0530, Nalin Bakshi wrote:
I have come on a problem regarding encryption. I am firing a simple
select statement:
select encrypt('\\','abcd','bf');
I want to use \ for encryption but I get the error:
invalid input syntax for type bytea
I tried using to encrypt \ , but on decryption I get \\ instead of
\ (single backslash).
The double backslash is the output representation of a single
backslash. See Table 8-7 bytea Literal Escaped Octets and Table
8-8 bytea Output Escaped Octets in the documentation:
http://www.postgresql.org/docs/8.2/interactive/datatype-binary.html
You can use length(), octet_length(), or encode() to see that the
decrypted value contains only a single octet:
test= select decrypt(encrypt(e'', 'abcd', 'bf'), 'abcd', 'bf');
decrypt
-
\\
(1 row)
test= select octet_length(decrypt(encrypt(e'', 'abcd', 'bf'), 'abcd',
'bf'));
octet_length
--
1
(1 row)
test= select encode(decrypt(encrypt(e'', 'abcd', 'bf'), 'abcd', 'bf'),
'hex');
encode
5c
(1 row)
Depending on your security requirements you might wish to use
pgp_sym_encrypt() or pgp_sym_encrypt_bytea() instead of encrypt().
See the Raw encryption section of README.pgcrypto for some of the
disadvantages of encrypt().
--
Michael Fuhr
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Slow query but can't see whats wrong
On Tue, Jul 24, 2007 at 10:40:16AM +0200, Henrik Zagerholm wrote: I'm using pgsql 8.2.4 and I have this query which is sooo slow but I can seem to figure out why. It is using the appropriate indexes but there is something wrong with the nested loops. I'm running auto vacuum so the statistics should be up to date. I've increase the statistics on most foreign keys. Have you examined the last_vacuum, last_autovacuum, last_analyze, and last_autoanalyze columns in pg_stat_user_tables to find out when the tables were last vacuumed and analyzed? The estimate on this index scan is a problem: - Index Scan using tbl_file_idx on tbl_file (cost=0.01..8.34 rows=1 width=39) (actual time=0.283..3339.003 rows=25039 loops=1) Index Cond: ((lower ((file_name)::text) ~=~ 'awstats'::character varying) AND (lower ((file_name)::text) ~~ 'awstatt'::character varying)) Filter: (lower ((file_name)::text) ~~ 'awstats%'::text) Is tbl_file_idx an index on the expression lower(file_name)? If so then I don't think increasing the statistics on tbl_file.file_name will help, but increasing the statistics on tbl_file_idx.pg_expression_1 might. You'll have to experiment to find a good value. ALTER TABLE tbl_file_idx ALTER pg_expression_1 SET STATISTICS 100; ANALYZE tbl_file; Unfortunately the statistics settings on index expressions don't survive a pg_dump. Fixing this has been discussed a few times but I don't think anybody has worked on it. The developers' TODO list has the following item: * Allow accurate statistics to be collected on indexes with more than one column or expression indexes, perhaps using per-index statistics -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] regexp_replace
On Mon, Jul 23, 2007 at 07:50:35AM -0700, [EMAIL PROTECTED] wrote: I would like to change a sub-string in a text-field by using UPDATE tablename SET fieldname=regexp_replace(fieldname,old_sub_string,new_sub_string) WHERE (fieldname like '%old_sub_string%') In priniciple, it works. However, only one occurence of old_sub_string is replaced and further not. Which syntax has to be used to replace all old_sub_strings by new_sub_string in a text-field at once? regexp_replace(fieldname, old_sub_string, new_sub_string, 'g') For more information search for regexp_replace in the Pattern Matching section of the Functions and Operators chapter of the documentation. http://www.postgresql.org/docs/8.2/interactive/functions-matching.html -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] shp2pgsql Help with sintax!
On Tue, Jul 17, 2007 at 09:46:42AM -0700, GPS Blaster wrote: Hi! New to postgres, im trying to import shapefiles into postgres 8.2 using shp2pgsql but so far have failed. What have you tried and how did it fail? You might get more help using PostGIS on postgis-users. http://postgis.refractions.net/mailman/listinfo/postgis-users I tryed using the -p option just to prepare / create the tables then insert the data, but no luck, please help me writing the correct syntax to acomplish for the following: Do want to create the table and import the data in separate steps? shp2pgsql's -p option will create the table without importing the data. Shapefile named callesnac.shp Database postgres (postgres default installation) Schema sky Need: Create table callesnac Add column gid integer / not null default / next val Add column clasificac_vias varchar shp2pgsql should create the gid serial column automatically. Is clasificac_vias in the data or is that a new column that you need to add? The following command will create the table without importing any data: shp2pgsql -p callesnac.shp sky.callesnac | psql postgres If you need to add another column then you could connect to the database and execute the following statement: ALTER TABLE sky.callesnac ADD COLUMN clasificac_vias varchar; import / insert all the data from callesnac.shp into callesnac table. shp2pgsql -aD callesnac.shp sky.callesnac | psql postgres The -a option means append. The -D option isn't required; it just tells shp2pgsql to use COPY instead of INSERT. COPY should be faster when importing large data sets. You could create the table and import the data in a single step, then add the new column afterwards: shp2pgsql -D callesnac.shp sky.callesnac | psql postgres psql -d postgres -c 'ALTER TABLE sky.callesnac ADD COLUMN clasificac_vias varchar;' You might need to use shp2pgsql's -s option to set the SRID, and you can use the -g option to specify a geometry column name other than the default of the_geom. If this doesn't help then please post the commands you're running and explain how the results differ from what you'd like. If you're getting errors then please post the exact text of the error messages. -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] encodings
On Sat, Jul 21, 2007 at 10:24:38PM +0200, Zlatko Matić wrote: If I have an UTF8 database, dump it and the restore as WIN1250 database, then dump it again and restore as UTF8, would structure of the database (schema) be exactly the same as initial database, or something will change in the process? In other words, does encoding influence only data stored in tables, or it influences database structure as well? I can't think of how the encoding would influence the structure. Are you seeing behavior that suggests otherwise? -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] posgres tunning
On Sat, Jul 21, 2007 at 09:54:35PM -0500, Arnaldo Gandol wrote: I have a drupal site working with postgres that does not tolerate more than 200 concurrent connections(it is not hardware fault). What does not tolerate mean? Does the database refuse connections beyond 200? Does it permit connections but performance suffers? Or something else? Does anybody know how to adjust postgres parameters and what are them?, or how to get a better performance in any way? We'll need to know more about the nature of the problem before we can make recommendations. For configuration guidelines see the performance-related documents at Power PostgreSQL: http://www.powerpostgresql.com/Docs -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Foreign key constraint question
On Fri, Jul 20, 2007 at 08:57:25AM -0500, Perry Smith wrote: I want to do something like this: ALTER TABLE companies ADD CONSTRAINT fk_companies_item_id FOREIGN KEY (item_id, 'Company') REFERENCES item_bases(item_id, item_type) INITIALLY DEFERRED I could add a column to companies that is always set to Company but that seems like a waste. I tried the above and I got a syntax error. What purpose is the constraint intended to achieve? -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] average/stddev on all values returned by a select distinct
On Tue, Jul 17, 2007 at 01:51:21PM +0100, ann hedley wrote: What I want is the average and stddev of the set of lengths returned by this query. Something like... select average(select distinct on (id) length(consensus) from cluster order by id,length(consensus) desc); I think this is what you're looking for: select avg(length) from ( select distinct on (id) length(consensus) from cluster order by id, length(consensus) desc ) s; -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] unconvertable characters
On Mon, Jul 16, 2007 at 04:20:22PM +0300, Sim Zacks wrote: My 8.0.1 database is using ISO_8859_8 encoding. When I select specific fields I get a warning: WARNING: ignoring unconvertible ISO_8859_8 character 0x00c2 Did any of the data originate on Windows? Might the data be in Windows-1255 or some encoding other than ISO-8859-8? In Windows-1255 0xc2 represents U+05B2 HEBREW POINT HATAF PATAH -- does that character seem correct in the context of the data? http://en.wikipedia.org/wiki/Windows-1255 I now want to upgrade my database to 8.2.4 and change the encoding to UTF-8. When the restore is done, I get the following errors: pg_restore: restoring data for table manufacturers_old pg_restore: [archiver (db)] Error from TOC entry 4836; 0 9479397 TABLE DATA manufacturers postgres pg_restore: [archiver (db)] COPY failed: ERROR: character 0xc2 of encoding ISO_8859_8 has no equivalent in UTF8 CONTEXT: COPY manufacturers_old, line 331 And no data is put into the table. Is there a function I can use to replace the unconvertable charachters to blanks? If the data is in an encoding other than ISO-8859-8 then you could redirect the output of pg_restore to a file or pipe it through a filter and change the SET client_encoding line to whatever the encoding really is. For example, if the data is Windows-1255 then you'd use the following: SET client_encoding TO win1255; Another possibility would be to use a command like iconv to convert the data to UTF-8 and strip unconvertible characters; on many systems you could do that with iconv -f iso8859-8 -t utf-8 -c. If you convert to UTF-8 then you'd need to change client_encoding accordingly. -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Vacuum issue
On Thu, Jul 05, 2007 at 01:17:13PM +0100, E.J. Moloney wrote: I have a database with a table that adds 3 records a day, I am delete records older than 2 days. I am vacuuming it once a day , I am having an issue that the disk usage is continually rising. i.e. the space been flagged as free by the vacuum process isn't being reused. Are you sure this table is responsible for the disk space increase? Might the growth be elsewhere? Does this table receive a lot of updates? Have you done a database-wide VACUUM VERBOSE and examined the free space map info at the end to see if you need to adjust your FSM settings? Have you checked whether the table's indexes are what's growing? Certain usage patterns can cause indexes to grow even if they're being vacuumed regularly; you might need to reindex periodically. Please find below a copy of the cron command being used. Have you checked the vacuumdb output to confirm that it's running successfully? su - $PGUSER -c $COMMAND --analyze -v $PGLOG 21 This command is vacuuming only one database, probably postgres (but check the output to be sure). Is that where your tables are? I am running Postgre 8.4 on a Susse 10.1 PostgreSQL (not Postgre) 8.4 doesn't exist; do you mean 8.2.4? -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Localization trouble
On Thu, Jul 05, 2007 at 05:10:57PM -0700, Chris Travers wrote: I am trying to find a way to select the number format at runtime for textual representation of numbers. I am currently running 8.1.4 built from source on Fedora Linux core 5. I have been trying to use set lc_numeric = various country codes (for example es_EC), but I am not able to get the format to change from 1.00 to 1,00. I think you'll need to use to_char(): test= set lc_numeric to 'es_ES.UTF-8'; SET test= select to_char(1.234, '9D999'); to_char - 1,234 (1 row) The file src/backend/utils/adt/pg_locale.c in the PostgreSQL source code has comments about how various LC_* settings are used in the backend. -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] shmctl EIDRM preventing startup
On Mon, Jul 02, 2007 at 01:05:35PM +0200, Martijn van Oosterhout wrote: If it's installed, this: lsof |grep SYSV Will list all processes attached to a SHM segemtn on the system. I think ipcs can do the same. You can grep /proc/*/maps for the same info. I already tried those; none show the shared memory key that the postmaster is complaining about. -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] tables are not listable by \dt
On Mon, Jul 02, 2007 at 10:04:21AM -0400, Rajarshi Guha wrote: Hi, we recently upgraded our Postgres instllation from 7.4 to 8.2 by doing a dump and restore. Howveer after logging into the database (as a user that is not the superuser) and doing \dt I get the error: No relations found But when I do SELECT relname, relpages FROM pg_class ORDER BY relpages DESC; I get a list of the tables and their sizes. Are the tables in schemas that are in your search_path? -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Trapping errors from pl/perl (trigger) functions
On Sat, Jun 30, 2007 at 10:30:32PM +0200, Wiebe Cazemier wrote: I have a pl/perl trigger function which can give an error, and I would like to catch it in a pl/pgsql function, but I can't seem to trap it. What have you tried and how did the outcome differ from your expectations? Is it possible to catch errors generated pl/perl functions in a BEGIN ... EXCEPTION WHEN ... END block? Or perhaps in some other way? You could use WHEN internal_error or WHEN others. If that doesn't work then please post a simple but complete example that shows what you're trying to do. -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Trapping errors from pl/perl (trigger) functions
On Sun, Jul 01, 2007 at 03:50:09PM -0400, Tom Lane wrote: IMHO the real problem with both RAISE and the plperl elog command is there's no way to specify which SQLSTATE to throw. In the case of the elog command I think you just get a default. That default is XX000 (internal_error): test= create function foo() test- returns void test- language plperl test- as $_$ test$ elog(ERROR, 'test error'); test$ $_$; CREATE FUNCTION test= \set VERBOSITY verbose test= select foo(); ERROR: XX000: error from Perl function: test error at line 2. LOCATION: plperl_call_perl_func, plperl.c:1076 The code around plperl.c:1076 is /* XXX need to find a way to assign an errcode here */ ereport(ERROR, (errmsg(error from Perl function: %s, strip_trailing_ws(SvPV(ERRSV, PL_na); I don't see any relevant TODO items. Would something like the following be appropriate? * Allow RAISE and its analogues to set SQLSTATE. -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] shmctl EIDRM preventing startup
One of the servers I use (RHEL AS 4; Linux 2.6.9-34.ELsmp x86_64) appears to be in the same state after a reboot as the server in the Restart after poweroutage thread from a few months ago: http://archives.postgresql.org/pgsql-general/2007-03/msg00738.php As in the thread, ipcs -a shows no postgres-owned shared memory segments and strace shows shmctl() failing with EIDRM. http://archives.postgresql.org/pgsql-general/2007-03/msg00743.php I have only limited access to the box and I haven't found out why it was rebooted. I don't think it was a scheduled reboot so it might have been due to a power outage. Has anybody figured out if this is a Linux kernel bug? I might have until Monday morning if anybody can suggest something to look at; after that the admins will probably reboot and/or remove postmaster.pid to get the database running again. Thanks. -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] shmctl EIDRM preventing startup
On Sun, Jul 01, 2007 at 10:06:58PM -0400, Tom Lane wrote: Michael Fuhr [EMAIL PROTECTED] writes: Has anybody figured out if this is a Linux kernel bug? I might have until Monday morning if anybody can suggest something to look at; after that the admins will probably reboot and/or remove postmaster.pid to get the database running again. Is it possible/reasonable/practical to (a) hold off longer than that and (b) get me access to the box? On Monday I'd have a chance to involve some Red Hat kernel folk in looking at it. Possibly; I'll see what I can do. How early Monday do you think everybody would be available? -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] LC_CTYPE and matching accented chars
On Wed, Jun 27, 2007 at 09:28:24AM +1200, Martin Langhoff wrote:
Alvaro Herrera wrote:
I think it would be much easier if you did something like
select * from test where lower(to_ascii(value)) = lower(to_ascii('martín'));
When to_ascii doesn't work (for example because it doesn't work in UTF8)
you may want to use convert() to recode the text to latin1 or latin9.
Well, with the example above to_ascii doesn't work.
select to_ascii(value) from test ;
ERROR: encoding conversion from UTF8 to ASCII not supported
And neither does convert
select convert(value using utf8_to_ascii) from test ;
ERROR: character 0xc3 of encoding MULE_INTERNAL has no equivalent
in SQL_ASCII
As Alvaro suggested, try converting to latin1 or latin9 and then
calling to_ascii:
select 'martin' = to_ascii(convert('martín', 'latin1'), 'latin1');
?column?
--
t
(1 row)
For other possibilities search the list archives for examples of
unaccent functions that normalize text to NFD (Unicode Normalization
Form D) and remove nonspacing marks. Here's a message with a couple
of PL/Perl functions:
http://archives.postgresql.org/pgsql-general/2007-01/msg00702.php
--
Michael Fuhr
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] alter table type from double precision to real
On Mon, Jun 25, 2007 at 12:35:11AM -0400, Tom Lane wrote:
Michael Fuhr [EMAIL PROTECTED] writes:
On Mon, Jun 25, 2007 at 09:51:30AM +0900, [EMAIL PROTECTED] wrote:
It seems that real takes 8 byte storage sizes.
Real is 4 bytes but other columns' alignment requirements might
result in no space being saved.
Even with no other columns involved, if you're on a machine with
MAXALIGN = 8 (which includes all 64-bit platforms as well as some
that aren't), the row width won't shrink.
I see table sizes shrink on 64-bit sparc and x86 architectures, as
in the following example that results in adjacent 4-byte columns.
Or am I misinterpreting what's happening?
test= create table test (col1 double precision, col2 integer);
CREATE TABLE
test= insert into test select 1.0, 1 from generate_series(1, 1);
INSERT 0 1
test= select pg_relation_size('test');
pg_relation_size
--
524288
(1 row)
test= alter table test alter col1 type real;
ALTER TABLE
test= select pg_relation_size('test');
pg_relation_size
--
450560
(1 row)
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] alter table type from double precision to real
On Mon, Jun 25, 2007 at 12:35:11AM -0400, Tom Lane wrote: As for that pg_dump measurement, the text form isn't going to get smaller ... 1.2 is the same length as 1.2. Non-text formats like -Fc should (or might) shrink, right? They appear to in the tests I've done. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] alter table type from double precision to real
On Mon, Jun 25, 2007 at 09:51:30AM +0900, [EMAIL PROTECTED] wrote: Real type takes 4 byte storage sizes and double precision takes 8 bytes. I altered a data type from double precision to real and vacuumed DB. Altering a column's type rewrites the table so vacuuming afterward shouldn't be necessary. But PostgreSQL's data disk usage did not shrinked. And pg_dump size remained same. It seems that real takes 8 byte storage sizes. Real is 4 bytes but other columns' alignment requirements might result in no space being saved. -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Trigger function that works with both updates and deletes?
On Mon, Jun 18, 2007 at 06:07:37PM -0700, novnov wrote: First, when a record is being deleted, OLD refers to the rec just deleted (or about to be deleted)? Correct. Second, while I could write two trigger functions, one dealing with add/update, the other with deletes, it's probably neater to have a single trigger function and have it discriminate am I being called for a delete, or an add/update? I don't know how to determine the type record change. In PL/pgSQL you can use TG_OP. See Trigger Procedures in the documentation: http://www.postgresql.org/docs/8.2/interactive/plpgsql-trigger.html -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] VACUUM ANALYZE extremely slow
Sergei Shelukhin [EMAIL PROTECTED] wrote: This is my first (and, by the love of the God, last) project w/pgsql and everything but the simplest selects is so slow I want to cry. Please post an example query and its EXPLAIN ANALYZE output. The pgsql-performance mailing list is a good place to discuss performance problems. This is especially bad with vacuum analyze - it takes several hours for a database of mere 15 Gb on a fast double-core server w/2Gb of RAM and virtually no workload at the moment. Maintenance work mem is set to 512 Mb. What other non-default configuration settings do you have? What version of PostgreSQL are you using and on what OS? What kind of disks and controllers do you have? Is there any way to speed up ANALYZE? Without it all the queries run so slow that I want to cry after a couple of hours of operation and with it system has to go down for hours per day and that is unacceptable. Why does the system have to go down? Are you running VACUUM FULL ANALYZE? If so then drop the FULL and do an ordinary VACUUM ANALYZE instead -- it should run faster and it doesn't require exclusive access to the table. As Christopher Browne mentioned, a bare ANALYZE (without VACUUM) should be fast even on large tables so if necessary you could run ANALYZE more often than VACUUM ANALYZE. Have you enabled autovacuum (or contrib/pg_autovacuum in 8.0 and earlier)? I sometimes prefer to run VACUUM ANALYZE manually but for many databases autovacuum is a good way to maintain statistics and clean up dead rows automatically. -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Normal distribution et al.?
On Mon, Jun 18, 2007 at 05:11:51AM +0200, Jan Danielsson wrote: Are there any statistical libraries for postgresql? I'd like to do something along the lines of: UPDATE foo SET value=value+normdistsample(10, 0.2) WHERE id=1; Somebody else has mentioned PL/R. For this particular example see also normal_rand() in contrib/tablefunc. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Which meta table contain the functions
On Fri, Jun 15, 2007 at 06:47:10PM -0500, Alfred Zhao wrote: I can get the function list via \df. Can someone tell me which meta table contain the function list? Thanks! http://www.postgresql.org/docs/8.2/interactive/catalog-pg-proc.html You can see the statements that psql runs by starting psql with the -E (--echo-hidden) option or by executing \set ECHO_HIDDEN. This is a helpful way to learn about the system catalogs. -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] precision of epoch
On Thu, Jun 14, 2007 at 04:40:12AM -0700, [EMAIL PROTECTED] wrote: I'd like to convert timestamps without timezone to unix epoch values with at least microseconds resolution. but when i do e.g.: select extract (epoch from timestamp without time zone 'Thu 14 Jun 05:58:09.929994 2007'); i get: 1181793489.92999 so i loose the last digit. I'd expect 1181793489.929994 EXTRACT's return type is double precision, which isn't precise enough to represent that many significant digits. Notice that removing a digit from the beginning gives you another digit at the end: test= SELECT '1181793489.929994'::double precision; float8 -- 1181793489.92999 (1 row) test= SELECT '181793489.929994'::double precision; float8 -- 181793489.929994 (1 row) You could convert the epoch value to numeric but you'll have to use a more complex expression; simply casting EXTRACT's result to numeric won't work. One possibility might involve floor and to_char(value, '.US'). -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Functions that return both Output Parameters and recordsets
On Mon, Jun 11, 2007 at 03:20:15PM +0200, Pavel Stehule wrote: it's not possible. PostgreSQL doesn't support multiple recordset. You have to have two functions. If you don't mind handling cursors then you could return multiple cursors from one function. See the PL/pgSQL documentation for an example (the example is at the bottom of the page). http://www.postgresql.org/docs/8.2/interactive/plpgsql-cursors.html -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] pg_hba.conf - md5
On Sat, Jun 09, 2007 at 02:43:06AM -0700, Vince wrote:
I want to access by postgre db over the internet. My pg_hba.conf if
setup to do this:
hostall all 0.0.0.0/0 md5
Now, what I don't understand is how does the md5 effect things?
It causes the password exchange between the client and the server
to hash the user's password with a salt (random value) that the
server sends. This prevents the password from being passed in the
clear and it aims to prevent replay attacks, where an attacker who
had sniffed a previous session could respond to the server's challenge
without knowing the password by resending the same response it had
seen before (such an attack would still work in the unlikely -- but
possible -- event that the attacker had sniffed a previous session
that used the same salt).
MD5 authentication works like this:
Client: username, databasename
Server: MD5 authentication, salt
Client: MD5(MD5(password || username) || salt)
The server performs the same calculation (the user's password is
typically already stored in the system catalogs as MD5(password ||
username). If the results match then authentication succeeds.
If I connect via php:
$db = pg_connect('host=xx.xx.xx.xx port=5433 dbname=MYDB user=postgres
password=mypass');
mypass being whatever my password is; is still set in plain text?
No.
Why don't I have to send the md5 version of the password to connect?
Because libpq (or whatever underlying library you're using) does
that for you.
If you want to allow connections over an open network then consider
using SSL and allowing only hostssl connections from everywhere
except trusted networks.
http://www.postgresql.org/docs/8.2/interactive/ssl-tcp.html
The server could optionally require the client to present a certificate
signed by a specific CA and the client could require the same of the
server; see the discussion of root.crt for more information.
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] setting login database
On Fri, Jun 08, 2007 at 09:50:10AM +0200, Samatha Kottha wrote: OK, I am not yet awake :-) Of course, the connection string has database name but some thing is not working on OGSA-DAI side. It is giving authorisation failure error! What's the exact error message? Is the authorization failure coming from the database? What do the database logs show? Are you sure you're using the correct username and password? What do you have in pg_hba.conf? -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] setting login database
On Thu, Jun 07, 2007 at 03:38:15PM +0200, Samatha Kottha wrote: We are trying to access a postgres database using a data integration tool. This data integration tool have options to specify the hostname, port, userid, and passwd of a database. But there is no way to supply the database name. What data integration tool is it? Are you sure it doesn't have an option to specify the database? So, I am thinking to set the database name to each user (default login database) so that they do not need to supply that info when they access data using data integration tool. In libpq applications a role's default database has the same name as the role; you can override that with the PGDATABASE environment variable. http://www.postgresql.org/docs/8.2/interactive/libpq-envars.html See also PGSERVICE, PGSYSCONFDIR, and the connection service file. http://www.postgresql.org/docs/8.2/interactive/libpq-pgservice.html -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] list all columns in db
On Thu, Jun 07, 2007 at 06:36:07PM -0400, Jon Sime wrote:
select n.nspname as table_schema, c.relname as table_name,
a.attname as column_name
from pg_catalog.pg_attribute a
join pg_catalog.pg_class c on (a.attrelid = c.oid)
join pg_catalog.pg_namespace n on (c.relnamespace = n.oid)
where c.relkind in ('r','v') and a.attnum 0
and n.nspname not in ('pg_catalog','information_schema')
order by 1,2,3
Don't forget and not a.attisdropped else you might get something
like
table_schema | table_name | column_name
--++--
public | foo| pg.dropped.2
public | foo| col1
public | foo| col3
(3 rows)
--
Michael Fuhr
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] Foreign keys and indexes
On Tue, Jun 05, 2007 at 11:49:20AM +0200, Marc Compte wrote: Does PostgreSQL create an implicit index also for foreign keys? or must I create it explicitly? PostgreSQL doesn't create an index on the referencing column(s) of a foreign key constraint; if you want an index then you'll need to create it yourself. -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] COPY error
On Mon, Jun 04, 2007 at 02:12:00PM -0400, ABHANG RANE wrote:
Im trying to load data from a file using copy command. At the end of
the data, I have appended copy statement as
copy tablename(col1, col2) from stdin with delimiter as '\t';
.\
COPY should go before the data and end-of-data (\. not .\) should
go after the data. Also, in text mode the default delimiter is the
tab character (\t) so you can omit it unless you prefer to be
explicit. Try something like this (with a tab after 2713):
copy tablename (col1, col2) from stdin;
2713{3.70952,1.45728,0.134339,3.99197,2.22381,-0.435095,6.9}
\.
--
Michael Fuhr
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] what to do when pg_cancel_backend() doesnt work?
On Mon, Jun 04, 2007 at 12:00:10PM -0400, Alvaro Herrera wrote: Rhys Stewart escribió: a more readable version What is this buffer() function? Looks like the PostGIS buffer() function, which calls GEOSBuffer() in the GEOS library, which is where the code might be stuck. http://postgis.refractions.net/docs/ch06.html#id2527029 http://geos.refractions.net/ -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] function retuning refcursor, order by ignored?
On Tue, May 29, 2007 at 05:18:42PM -0700, novnov wrote: It seems that at least in the way I've written the function below, ORDER BY is ignored. Works here (see below). Can you post a complete example that shows ORDER BY being ignored? Do you get different results from the cursor than if you execute the same query directly? What version of PostgreSQL are you running? CREATE TABLE t_item ( item_id integer PRIMARY KEY, item_nametext NOT NULL, item_org_id integer NOT NULL, item_active boolean NOT NULL ); INSERT INTO t_item VALUES (4, 'four', 1, true); INSERT INTO t_item VALUES (2, 'two', 1, true); INSERT INTO t_item VALUES (1, 'one', 1, true); INSERT INTO t_item VALUES (3, 'three', 1, true); BEGIN; SELECT proc_item_list(1, true); proc_item_list unnamed portal 1 (1 row) FETCH ALL FROM unnamed portal 1; item_id | item_name | item_org_id | item_active -+---+-+- 1 | one | 1 | t 2 | two | 1 | t 3 | three | 1 | t 4 | four | 1 | t (4 rows) COMMIT; -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Different sort order
On Mon, May 28, 2007 at 07:07:41PM +0200, Poul Møller Hansen wrote: I'm wondering why the sort order on these two servers behaves differently. What's the output of the following query on each server? select name, setting from pg_settings where name ~ '^lc_'; -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Possible DB corruption
On Fri, May 25, 2007 at 04:47:52PM -0400, Justin M Wozniak wrote: We noticed that some records were mysteriously disappearing from our DB. I went in with psql and found that the \dt command no longer works, providing the output below. Is this a sure sign of DB corruption? Running psql (PostgreSQL) 7.4.8 on Linux 2.4.27. You might be suffering from transaction ID wraparound. Are you vacuuming regularly? http://www.postgresql.org/docs/7.4/interactive/maintenance.html#VACUUM-FOR-WRAPAROUND ...every table in the database must be vacuumed at least once every billion transactions. -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Possible DB corruption
On Mon, May 28, 2007 at 04:14:14PM -0600, Michael Fuhr wrote: On Fri, May 25, 2007 at 04:47:52PM -0400, Justin M Wozniak wrote: We noticed that some records were mysteriously disappearing from our DB. I went in with psql and found that the \dt command no longer works, providing the output below. Is this a sure sign of DB corruption? Running psql (PostgreSQL) 7.4.8 on Linux 2.4.27. You might be suffering from transaction ID wraparound. Another possibility: has search_path changed? You said that \dt shows nothing but you can still query the tables -- do you use schema-qualified table names in those queries? -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Problems with anyelement after upgrading from 8.1.4 to 8.1.9
On Sun, May 27, 2007 at 12:02:35PM +0200, Rafael Martinez wrote: Until postgresql 8.1.4, this has been working without problems. Yesterday I upgraded this database to 8.1.9 and select queries using the IF function fail with this error message: ERROR: cannot display a value of type anyelement I think this has already been fixed in CVS: http://archives.postgresql.org/pgsql-hackers/2007-05/msg00014.php http://archives.postgresql.org/pgsql-committers/2007-05/msg00011.php -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] change database encoding without corrupting data (latin9 to utf8)
On Fri, May 18, 2007 at 02:46:26AM -0700, filippo wrote: I have a problem to entry data to postgres database (latin9) from my perl/tk application running on windows (utf8). Whenever I try to entry letter with accents, these looks corrupted once stored into database. A workaround is to set client encoding to UTF8 after creating the database connection: $dbh-do(qq/SET client_encoding to 'UTF8'/); Workaround has a negative connotation that's perhaps misused in this case because setting client_encoding is the proper way of telling the database what the client's encoding is. If the connecting role will always use UTF8 then you could use ALTER ROLE (or ALTER USER in 8.0 and earlier) to automatically set client_encoding for every connection: ALTER ROLE rolename SET client_encoding TO 'UTF8'; To avoid such kind of workaround I'd like to convert the whole database from LATIN9 to UTF8, how can I do it without corrupting the data? If all of the data is uncorrupted LATIN9 then you could use pg_dump to dump the LATIN9 database and then restore it into a UTF8 database. But if you have a mix of uncorrupted and corrupted characters (UTF8 byte sequences stored as LATIN9) then you have a bit of a problem because some data needs to be converted from LATIN9 to UTF8 but other data is already UTF8 and shouldn't be converted. -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] autovacuum
On Sat, May 12, 2007 at 03:48:14PM -0400, Tom Allison wrote: I noticed that under 8.2 the autovacuum isn't running (logging) every 60s like I'm used to seeing. See the 8.2 Release Notes: http://www.postgresql.org/docs/8.2/interactive/release-8-2.html * Remove routine autovacuum server log entries (Bruce) pg_stat_activity now shows autovacuum activity. In 8.2 the autovacuum: processing database messages are logged at DEBUG1; in 8.1 they were logged at LOG. I pretty much just took the defaults in the postgresql.conf file since that's always seemed to work before. Autovacuum was first incorporated into the backend in 8.1 and it's disabled by default in 8.1 and 8.2, at least in source builds (it might be enabled by default in some pre-packaged distributions). What do you have in postgresql.conf for the following settings? autovacuum autovacuum_naptime stats_start_collector stats_row_level log_min_messages Do you see any warnings like the following in the server logs? WARNING: autovacuum not started because of misconfiguration HINT: Enable options stats_start_collector and stats_row_level. I'm not making a lot of changes to the database right now (insert/ update/delete) but I thought I would still get the logging. If you have autovacuum and row-level statistics enabled then autovacuum should be running. I'd guess you aren't seeing the routine messages because they're logged at DEBUG1 and you have log_min_messages at a level that doesn't show debug messages. -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Printing values on pgadmin tool window using plperlu
On Sun, May 13, 2007 at 11:51:55PM -0400, Harpreet Dhaliwal wrote: I have a function written in language plpelu. Normally, values in perl code are printed using print statement. Then same when used in postgres environment won't print messages in 'Messages' tab to pgadmin query tool window. How and where can I print these values while running the plperlu function. Use elog(). http://www.postgresql.org/docs/8.2/interactive/plperl-database.html -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] IP Address Validation
On Thu, May 10, 2007 at 12:22:37AM -0400, Jasbinder Singh Bali wrote:
I need to check whether the input string is in ip address format or not in
one of my pl/pgsql functions.
What function should be used to accomplish this
I tried using
if inet(strInput)
However, it throws an exception if the input string is not in IP address
format.
I could have caught this exception but really don't know what type of
exception category would this exception come under..
psql test
test= \set VERBOSITY verbose
test= select inet('junk');
ERROR: 22P02: invalid input syntax for type inet: junk
http://www.postgresql.org/docs/8.2/interactive/errcodes-appendix.html
22P02 INVALID TEXT REPRESENTATION invalid_text_representation
--
Michael Fuhr
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] FIN_WAIT_2
On Tue, Apr 24, 2007 at 06:32:31PM -0400, [EMAIL PROTECTED] wrote: I installed postgresql 8.2.3 in a freebsd server, my client application is written in C++ builder + zeoslib and I haven't any problem until now, but now with 8.2.3 version I have many connection that remains in FIN_WAIT_2, any suggest? Which side of the connection is in FIN_WAIT_2? What's the netstat output for both sides? What can you tell us about how this application works? -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] pg_dump seg fault on sequences
On Sat, Apr 14, 2007 at 06:26:22PM -0400, Tom Lane wrote: Michael Nolan [EMAIL PROTECTED] writes: I've narrowed down the conditions under which pg_dump in 8.2.3 is creating a segmentation fault. It appears to happen only when dumping a sequence that is created for a serial data element. Thanks for the test case --- I can reproduce this now on 8.2 and HEAD too. Will take a look tomorrow if no one beats me to it. (gdb) bt #0 0x0001e500 in dumpTable (fout=0x53498, tbinfo=0x8c7e0) at pg_dump.c:8733 #1 0x0001f12c in dumpDumpableObject (fout=0x53498, dobj=0x8c7e0) at pg_dump.c:5009 #2 0x00020dd8 in main (argc=4, argv=0x6e) at pg_dump.c:691 (gdb) l 8733,+1 8733appendPQExpBuffer(query, .%s;\n, 8734fmtId(owning_tab-attnames[tbinfo-owning_col - 1])); (gdb) p owning_tab-attnames $1 = (char **) 0x0 -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Do I need serializable for this query?
On Tue, Apr 10, 2007 at 10:52:11PM +0200, Peter Eisentraut wrote: William Garrison wrote: I have a table that keeps running totals. It is possible that this would get called twice simultaneously for the same UserID. Do I need to put this in a serializable transaction? Transaction isolation is only a question of interest if you have more than one statement in a transaction. Not true; the isolation level is also relevant if you're doing concurrent updates or deletes (although, as the documentation mentions, PostgreSQL's serializable isolation level doesn't guarantee true serializability). create table test (id integer primary key, t text); insert into test (id, t) values (1, 'a'); T1: begin isolation level serializable; T2: begin isolation level serializable; T1: update test set t = 'b' where id = 1; T2: update test set t = 'c' where id = 1; -- blocks T1: commit; T2: ERROR: could not serialize access due to concurrent update -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] performance; disk bad or something?
On Sat, Apr 07, 2007 at 09:28:58AM +0200, Marcus Engene wrote: INFO: apa_item_common: removed 9028 row versions in 3651 pages DETAIL: CPU 0.24s/0.36u sec elapsed 30.69 sec. INFO: apa_item_common: found 9028 removable, 12863 nonremovable row versions in 14489 pages DETAIL: 0 dead row versions cannot be removed yet. There were 76646 unused item pointers. How often does this table receive updates and deletes and how often are you vacuuming it? It averages less than one row per page (12863 nonremovable row versions in 14489 pages) and appears to have become quite bloated with dead rows sometime in the past (76646 unused item pointers). Use CLUSTER or VACUUM FULL + REINDEX to compact the table and run ANALYZE afterwards to update the statistics, then make sure you're vacuuming it often enough to keep it from becoming bloated again. Is your free space map sufficiently sized? If you do a database-wide VACUUM VERBOSE, what are the last few lines of the output that mention free space map settings? -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] performance; disk bad or something?
On Sat, Apr 07, 2007 at 01:49:38PM +0200, Marcus Engene wrote: Michael Fuhr skrev: How often does this table receive updates and deletes and how often are you vacuuming it? If I should take a guess, there are 5 deletes per day and 5 updates or inserts per hour. The table is 1.5 years old and I try to vacuuming it once a week; although without full. I normally do a reindex as well. The first VACUUM output showed 9028 removable and 12863 nonremovable rows, which indicates more activity than your guess. I'd suggest vacuuming the table (without FULL) more often than once per week; this table is small enough that vacuuming should take at most a few seconds if you do it often enough. One way to ensure that this happens is to use cron (or whatever your system's scheduler is) to run VACUUM ANALYZE nightly or more often. Or use autovacuum, which is available in 8.0 and earlier as a contributed module and in 8.1 and later as a core component (but not enabled by default). I've googled a bit to find optimizer hints a la oracle's /*+ index(asdasd) */ but from what I can tell pg has chosen not to use that? I find them convenient for testing at least, even if I agree that one perhaps should avoid having them in a final product. You can influence the query planner with configuration settings. Here's the relevant section in the 8.0 documentation: http://www.postgresql.org/docs/8.0/interactive/runtime-config.html#RUNTIME-CONFIG-QUERY I'd recommend increasing effective_cache_size if you're still using the default. Most of the remaining settings are best used only for debugging unless you can't get a reasonable query plan any other way, and then I'd recommend changing settings only for specific queries (and resetting them after those queries) and not changing the system-wide values. The original select seems to be consistantly fast now. That is good, but do I have a ticking bomb? 12k rows is little by any measure and if it was so slow by a little bloat it will be inevitable to reoccur again? The table was more than a little bloated -- VACUUM FULL shrunk it from 14489 pages to 1832 pages, which means that the table was nearly 8 times larger than it needed to be. You could get an idea of the amount of bloat from the first VACUUM output: INFO: apa_item_common: found 9028 removable, 12863 nonremovable row versions in 14489 pages DETAIL: 0 dead row versions cannot be removed yet. There were 76646 unused item pointers. The table had almost as many dead rows (9028) as live rows (12863) and it had additional space (76646) for many times more rows than the table contained. If you vacuum often enough then tables shouldn't become bloated. Autovacuum can help in this respect. Is 8.2.x better at these simple things too or is it mainly complex multithreadable queries which will benefit from it? 8.1 and later have autovacuum as a core component; if you enable it then the database will vacuum tables as needed (in 8.1 you might want to lower the default scale factors and thresholds; 8.2's defaults are halved compared to 8.1's). New major releases have other improvements such as better query planning. I hadn't touched any fsm settings but I've now set it to max_fsm_pages = 20 # min max_fsm_relations*16, 6 bytes each 20k max_fsm_relations = 1 # min 100, ~50 bytes each 1k The output of a database-wide VACUUM VERBOSE will indicate whether the fsm settings are high enough. -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] problem selecting from function
On Fri, Apr 06, 2007 at 03:52:17AM +0200, Rikard Pavelic wrote: When I select from this function I get an error ERROR: record red has no field id [...] create function select_ex1(out id int, out name1 varchar, out value1 int) returns setof record as $$ declare red record; begin for red in select id, name1, value1 from example1 LOOP id=red.id; name1=red.name1; value1=red.value1; return next; The columns in the select list match the parameter names so you're selecting the parameters, not the columns in example1. The query is effectively: for red in select NULL, NULL, NULL from example1 loop The code should work if you qualify the columns: for red in select e.id, e.name1, e.value1 from example1 e loop -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] YTA Time Zone Question
On Thu, Apr 05, 2007 at 05:52:02PM -0700, Danny Armstrong wrote:
If ruby and python tell me the value I just inserted into the db,
1174773136, is Sat Mar 24 21:52:16 UTC 2007, then I expect that
set time zone 0; -- format as though I'm in utc
select measurement_time
from table
will also yield that time.
Instead it yields 2007-03-25 04:52:16+00, which means it interprets
the original value as local time (I'm PDT), and then formats it as UTC
by adding 7 hours to it.
Turn on statement logging and see what the Ruby and Python drivers
are executing. Here's an example Ruby script:
require 'dbi'
require 'time'
t = Time.parse('Sat Mar 24 21:52:16 UTC 2007')
dbh = DBI.connect('dbi:Pg:dbname=test', 'user', 'password')
dbh.do('INSERT INTO foo (t1, t2) VALUES (?, ?)', t, t.to_s)
dbh.disconnect
When I run this script the database logs the following:
INSERT INTO foo (t1, t2) VALUES ('2007-03-24 21:52:16', 'Sat Mar 24 21:52:16
UTC 2007')
Notice that the first value (of class Time) is sent without a
timezone; the database therefore interprets it according to the
database's timezone setting (US/Pacific in my test environment).
The second value (of type String) includes the timezone so the
database interprets it as expected.
test= SET timezone TO 'UTC';
test= SELECT t1, t2 FROM foo;
t1 | t2
+
2007-03-25 04:52:16+00 | 2007-03-24 21:52:16+00
(1 row)
test= SET timezone TO 'US/Pacific';
test= SELECT t1, t2 FROM foo;
t1 | t2
+
2007-03-24 21:52:16-07 | 2007-03-24 14:52:16-07
(1 row)
The Python driver you're using might behave the same way. I'd
suggest contacting driver authors.
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] No of triggers of one single table
On Fri, Apr 06, 2007 at 01:10:13PM -0400, Harpreet Dhaliwal wrote: Can i have more than one trigger on one single table. Actually I want 2 different events to take place simultaneously and independently after insert. What happened when you tried it? -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Print database name
On Thu, Apr 05, 2007 at 08:51:28AM -0400, Woody Woodring wrote: Is there a sql command to print out which database I am connected to? SELECT current_database(); See System Information Functions in the documentation for other such functions. http://www.postgresql.org/docs/8.2/interactive/functions-info.html -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Strange result using transactions
On Tue, Mar 27, 2007 at 12:41:53PM +0200, Matthijs Melissen wrote: I get DELETE 0 even if a record with id=20 already exists before both transactions. Transaction 2 (T2) is deleting the version of the row with id = 20 that was visible to T2 when it executed its DELETE. Since T1 deleted that version of the row first, T2 finds no row to delete after T1 commits and releases its locks. T2 doesn't know about the row that T1 inserted because T1 hadn't committed yet when T2 executed its DELETE. Run T2 as a Serializable transaction and you'll see different behavior: 1) begin; 1) delete from forum where id = 20; 1) insert into forum (id, name) values (20, 'test'); 2) begin isolation level serializable; 2) delete from forum where id = 20; 1) commit; When T1 commits T2 should fail with SQLSTATE 40001 SERIALIZATION FAILURE (could not serialize access due to concurrent update). T2 still doesn't know about the row that T1 inserted but now T2 knows that something happened to the version of the row it was trying to delete. -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How can I select a comment on a column?
On Mon, Mar 26, 2007 at 04:37:59PM +0200, Serguei Pronkine wrote: How can I now retrieve column number from column name? Query pg_attribute. http://www.postgresql.org/docs/8.2/interactive/catalog-pg-attribute.html Since psql can display object comments you could study the SQL that it executes: \set ECHO_HIDDEN \d+ mytab -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Query not using index pgsql 8.2.3
On Fri, Mar 23, 2007 at 11:43:54AM +0100, Henrik Zagerholm wrote: I'm having a hard time understanding why my query is not using my indices when I change a field in the WHERE clause. The row count estimate for fk_filetype_id = 83 is high by an order of magnitude: Bitmap Index Scan on tbl_file_idx6 (cost=0.00..25.65 rows=1251 width=0) (actual time=21.958..21.958 rows=112 loops=1) Index Cond: (fk_filetype_id = 83) Have you run ANALYZE or VACUUM ANALYZE on these tables recently? If so then you might try increasing the statistics target for tbl_file.fk_filetype_id and perhaps some of the columns in the join conditions. -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Query not using index pgsql 8.2.3
On Fri, Mar 23, 2007 at 12:41:58PM +0100, Henrik Zagerholm wrote: 23 mar 2007 kl. 12:33 skrev Michael Fuhr: The row count estimate for fk_filetype_id = 83 is high by an order of magnitude: Bitmap Index Scan on tbl_file_idx6 (cost=0.00..25.65 rows=1251 width=0) (actual time=21.958..21.958 rows=112 loops=1) Index Cond: (fk_filetype_id = 83) Have you run ANALYZE or VACUUM ANALYZE on these tables recently? If so then you might try increasing the statistics target for tbl_file.fk_filetype_id and perhaps some of the columns in the join conditions. I did a vacuum full and reindex on all tables. VACUUM FULL is seldom (if ever) necessary if you're running plain VACUUM (without FULL) often enough, either manually or via autovacuum. Now I also did a vacuum analyze on tbl_acl (the biggest table with about 4.5 millin rows) Same result. I'd suggest analyzing all tables. The bad estimate I mentioned appears to be for a column in tbl_file so if you didn't analyze that table then the query plan probably won't improve. But do you mean if the row_count estimate is big it can't use any index on any other table within the JOINs? High row count estimates make the planner think that scanning entire tables would be faster than using indexes. The more of a table a query must fetch the less efficient an index scan becomes, to the point that a sequential scan is faster than an index scan. Any specific parameters I should adjust? If analyzing the tables doesn't improve the row count estimates then try increasing some columns' statistics targets and re-analyze the table or just that column. Example: ALTER TABLE tbl_file ALTER fk_filetype_id SET STATISTICS 100; ANALYZE tbl_file (fk_filetype_id); The default statistics target is 10; the maximum value is 1000. On some systems I've found that reducing random_page_cost from 4 (the default) to 2 gives more realistic cost estimates for index scans. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Query not using index pgsql 8.2.3
On Fri, Mar 23, 2007 at 02:13:57PM +0100, Henrik Zagerholm wrote: I'm just amazed that setting the statistics threshold on one column mad all the difference. IS there any guidelines on what columns I should change the statistics on? Start by looking for columns involved in simple comparisons with a constant -- especially if you know that the distribution of values is uneven -- and see how far off the row count estimate is for various values. You can use simple queries such as EXPLAIN ANALYZE SELECT 1 FROM tbl_file WHERE fk_filetype_id = 83; To learn more read How the Planner Uses Statistics in the documentation: http://www.postgresql.org/docs/8.2/interactive/planner-stats-details.html Among the configuration settings to consider changing are shared_buffers, effective_cache_size, work_mem, and random_page_cost. The following is a good starting point: http://www.powerpostgresql.com/PerfList If you have additional performance-related questions then consider posting to pgsql-performance. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] CHAR data type
On Wed, Mar 21, 2007 at 11:29:54AM -0300, Leticia wrote: If I use char(8000) instead of varchar(8000) why there is no padding and these three tuples are inside the same page? http://www.postgresql.org/docs/8.2/interactive/datatype-character.html The storage requirement for data of these types is 4 bytes plus the actual string, and in case of character plus the padding. Long strings are compressed by the system automatically, so the physical requirement on disk may be less. Long values are also stored in background tables so they do not interfere with rapid access to the shorter column values. See also the TOAST documentation: http://www.postgresql.org/docs/8.2/interactive/storage-toast.html -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Exception handling in plperl
On Tue, Mar 13, 2007 at 11:23:03PM -0400, Jasbinder Singh Bali wrote: I have a stored procedure written in plperl. This procedure has series of inserts at various levels. A few inserts on certain tables invoke triggers that launch tools outside the domain of the database. How can I make everything as one single transaction and simply roll back everything whenever an exception occurs. Statements are always executed in a transaction; if you're not inside an explicit transaction block then statements will be implicitly wrapped in a transaction for you. If the outermost statement is SELECT function_that_does_inserts() then everything that happens inside that function is part of the same transaction, and if any of the function's statements fail then the entire transaction will fail unless you trap the error. In plperlu you can trap errors with eval; see the Perl documentation for more information. Are you wanting to trap errors so you can roll back actions that happened outside the database? If so then you could use eval to handle failures, then do whatever cleanup needs to be done (and can be done) outside the database, then use elog to raise an error and make the current transaction fail. However, if this is what you're trying to do then understand that actions outside the database aren't under the database's transaction control and might not be able to be rolled back. If I've misunderstood what you're asking then please provide more information about what you're trying to do. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Incorrect ERROR: database xxx is being accessed by other users
On Tue, Mar 13, 2007 at 11:41:46AM +0300, Dmitry Koterov wrote: Yes, I have one! How to remove it now? I tried DEALLOCATE for gid returned by select * from pg_prepared_xacts; but it says prepared statement does not exist... DEALLOCATE is for prepared *statements*; you have a prepared *transaction*. Connect to the database you're trying to drop and use ROLLBACK PREPARED or COMMIT PREPARED, then disconnect from that database and try dropping it again. -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] DBD::Pg/perl question, kind of...
On Mon, Mar 12, 2007 at 08:38:52AM -0400, Douglas McNaught wrote: You are restricted to staying in a transaction while the cursor is open, so if you want to work outside of transactions LIMIT/OFFSET is your only way. http://www.postgresql.org/docs/8.2/interactive/sql-declare.html If WITH HOLD is specified and the transaction that created the cursor successfully commits, the cursor can continue to be accessed by subsequent transactions in the same session. (But if the creating transaction is aborted, the cursor is removed.) A cursor created with WITH HOLD is closed when an explicit CLOSE command is issued on it, or the session ends. In the current implementation, the rows represented by a held cursor are copied into a temporary file or memory area so that they remain available for subsequent transactions. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Solaris and Ident
On Fri, Mar 09, 2007 at 10:29:46AM -0800, D Unit wrote: I can't get the .pgpass file working. I think the problem may have to do with the fact that the user's home directory is '/'. Is there a way to specify a different location for .pgpass other than '~/.pgpass'? Set the PGPASSFILE environment variable. Also, make sure group and world have no permissions on the file. http://www.postgresql.org/docs/8.2/interactive/libpq-pgpass.html http://www.postgresql.org/docs/8.2/interactive/libpq-envars.html -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Unable to restore dump due to client encoding issues -- or, when is SQL_ASCII really UTF8
On Tue, Feb 27, 2007 at 08:43:27AM -0500, Bill Moran wrote: First off, it's my understanding that with SQL_ASCII encoding, that PostgreSQL does no checking for valid/invalid characters, per the docs: http://www.postgresql.org/docs/8.2/static/multibyte.html Correct. As the documentation says, SQL_ASCII is not so much a declaration that a specific encoding is in use, as a declaration of ignorance about the encoding. The beginning of the dump file I am restoring has the following: -- -- PostgreSQL database dump -- SET client_encoding = 'SQL_ASCII'; [...] But when I try to pull the dump in with psql, I get the following errors: ERROR: invalid byte sequence for encoding UTF8: 0xa0 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by client_encoding. Connecting to the database and issuing show client_encoding shows that the database is indeed set to SQL_ASCII. client_encoding doesn't show the database encoding, it shows the client encoding; execute show server_encoding to see the database encoding. You can also use psql -l or \l from within psql to see all databases and their encodings. The error suggests that the database encoding is UTF8. Now ... I'm expecting the server to accept any byte sequence, since we're using SQL_ANSII, but that is (obviously) not the case. Am I missing something obvious here? Grepping the entire dump file shows absolutely no references to UTF8 ... so why is the server trying to validate the byte string as UTF8? Probably because the database is UTF8 (see above). Either create the database as SQL_ASCII (see createdb's -E option) or change the client_encoding setting in the dump to whatever the encoding really is (probably LATIN1 or WIN1252 for Western European languages). -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Unable to restore dump due to client encoding issues -- or, when is SQL_ASCII really UTF8
On Tue, Feb 27, 2007 at 10:31:47AM -0500, Bill Moran wrote: The database was, indeed, UTF8, which is the default on newer Postgres. initdb determines the encoding from your environment. If you're on a Unix-like platform run env | grep UTF and you'll probably see that LANG and/or LC_* is set to something like en_US.UTF-8. I find it odd that I can't alter the encoding after database creation, but in this case it wasn't a big deal, as it's a lab system and I can just back up a few steps and start over. Altering the database encoding would mean that all string data would have to be checked and possibly converted. Doing that on a large running system would be problematic; it would probably be just as easy to dump and restore the entire database. -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] some tables unicode, some ascii?
On Sat, Feb 24, 2007 at 11:35:10PM -0500, Gene wrote: Is there a way to get the benefit of using ascii and the C locale for index usage on certain tables and unicode encoding on others (where peformance is not an issue)? In particular the planner will not use index with Unicode on like '123%' queries. See Operator Classes in the Indexes chapter of the documentation. http://www.postgresql.org/docs/8.2/interactive/indexes-opclass.html -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
