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] 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] 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 (1 row) FETCH ALL FROM ""; 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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 2>&1 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] 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 -- 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] 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] 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] 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] 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] 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] 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] 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] 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: 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] 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: [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] [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] 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] 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] 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] 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] 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] 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] 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] 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] 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 . 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] 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
> John Smith, but what I get is:
>
> pgslekt=> select REGEXP_REPLACE('[p=1242|John Smith]',
> pgslekt(> E'[p=(\d+)|(.+?)]',
> pgslekt(> E'\\2');
> regexp_replace
> --
> [=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'\\2'
);
regexp_replace
---
John Smith
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'\\2' > 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 . 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
> ---
> John Smith] and [p=456|Jane Doe
> (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
> > ---
> > John Smith] and [p=456|Jane Doe
> > (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'\\2',
'g'
);
regexp_replace
-----
John Smith and Jane Doe
--
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] 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] 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] 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] 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] 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] 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 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 > > 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] 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] 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] 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] 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 , 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 . 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 ) 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 . > 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 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] error while loading shared libraries: libpq.so.3
On Mon, Feb 21, 2005 at 12:47:58PM +0530, Surabhi Ahuja wrote: > cc -I/usr/local/pgsql/includesample.c -L/usr/local/pgsql/lib -lpq -o > sample > I then run itand it gives and error > [indra:/homes/surabi] ./sample > ./sample: error while loading shared libraries: libpq.so.3: cannot open > shared object file: No such file or directory You probably need to specify a runtime library search path option like -R or -rpath (see your compiler or linker documentation). For example, the following might work: cc -L/usr/local/pgsql/lib -R/usr/local/pgsql/lib ... See also the manual page for the ldd command if your system has it. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] error while loading shared libraries: libpq.so.3
On Mon, Feb 21, 2005 at 02:22:20PM +0530, Surabhi Ahuja wrote: > I give the following command now to compile sample.c > gcc -o sample sample.c -I/usr/local/pgsql/include -L/usr/local/pgsql/lib > -rpath/usr/local/pgsql/lib - lpq > > it gives this message: > gcc: unrecognized option `-rpath/usr/local/pgsql/lib' > (the same happens if i give -R instaed of -rpath As I said, see your compiler or linker documentation for the correct option. Another possibility might be "-Wl,-rpath=/usr/local/pgsql/lib", but *see the documentation* instead of using trial-and-error. What's happening is that the run-time linker doesn't know where to find libpq. You can fix that a few different ways: 1. You can add run-time link information when you build the program. That's what the aforementioned options are for; see your documentation to find out the correct way to do it. Some people consider this the "right" way to solve the problem. 2. You can globally change the run-time linker's configuration. How to do this depends on the platform, so again, you'll have to consult your local documentation. 3. You can use an environment variable like LD_LIBRARY_PATH to tell the run-time linker what additional directories to search. Many people consider this a workaround rather than a fix and therefore suggest avoiding it in favor of (1) or possibly (2). http://www.visi.com/~barr/ldpath.html NOTE: This is a build procedure issue that has nothing to do with PostgreSQL, aside from the fact that in this particular case you're trying to link against a PostgreSQL library. You'll have the same problem any time you link against a shared library that's not in the run-time linker's path. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] libpq & its header files
On Mon, Feb 21, 2005 at 01:27:58PM -0800, Mohsen Pahlevanzadeh wrote: > Dears,I have started to working with libpq.But i don't know name of its > header files.Please guide me. See the libpq documentation for the version of PostgreSQL you're using. Here's a link to the most recent version: http://www.postgresql.org/docs/8.0/static/libpq.html The introductory text mentions the header file that programs need to include. See also the "Building libpq Programs" and "Example Programs" sections. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] tables & retrieving their names
On Mon, Feb 21, 2005 at 03:45:42PM -0800, Mohsen Pahlevanzadeh wrote: > 2.How i see name of my tables in shell? > 3.How i see name of my DBs in shell? See the psql documentation: http://www.postgresql.org/docs/8.0/static/app-psql.html See also "System Catalogs" and "The Information Schema": http://www.postgresql.org/docs/8.0/static/catalogs.html http://www.postgresql.org/docs/8.0/static/information-schema.html > 4.How i retrieve tables's name in C? > 5.How i retrieve DBs's name in C? In addition to the above, see "libpq - C Library" in the documentation: http://www.postgresql.org/docs/8.0/static/libpq.html If you're new to PostgreSQL, it might be useful to go through the Tutorial and then skim the rest of the documentation. The FAQ might also answer some of your questions. http://www.postgresql.org/docs/ -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] How to Prevent Certain Kinds of Joins?
On Tue, Feb 22, 2005 at 02:51:09PM -0500, Heather Johnson wrote: > Is there a way to revoke permission to join two or more tables, even for > users who have all other permissions (e.g., select, insert, update, > delete) on those tables? For what purpose? If this were possible, then users could still do joins on the client side if they had access to all the data. Are you just trying to prevent potentially large queries? What problem are you trying to solve? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] tsearch2 problems / limitations
On Tue, Feb 22, 2005 at 01:29:45PM +, James Croft wrote:
> One of the tables holds reasonable amounts of text, some fields hold up
> to 2Mb. When I try and run
>
> UPDATE table SET idxfti=to_tsvector('default', field);
>
> it runs for a while then aborts with the following message
>
> ERROR: value is too big
README.tsearch2 says that the maximum size of a tsvector is 2^20
bytes (1M). You can see that limit enforced in the makevalue()
function in tsvector.c (similarly in pushquery() in query.c):
if (cur - str > MAXSTRPOS)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("value is too big")));
MAXSTRPOS is defined in tsvector.h:
#define MAXSTRPOS ( 1<<20 )
Maybe Oleg will reply and say whether it's safe to change that
or not.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Help with queries...
On Thu, Feb 24, 2005 at 02:33:28PM -0600, Cristian Prieto wrote: > select * from users; > ERROR: relation "users" does not exist > > but: > select * from "Users"; > returns all the data I want... See "Identifiers and Keywords" in the "SQL Syntax" chapter of the documentation, especially the parts that talk about case and quoting: http://www.postgresql.org/docs/8.0/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Referencing created tables fails with message that they do not exist!
On Sun, Feb 27, 2005 at 06:50:50PM -0500, Tommy Svensson wrote: > SELECT * FROM ; > SELECT * FROM public.; > SELECT * FROM .public.; > > All result in the message "The relation does not exist!" or "The > relation public. does not exist!". Could you copy and paste the *exact* commands and error messages and send them to the list? That might help us see what's going on. > The tables do actually get created. I can se them in DBVisualizer. If you run psql, what does \d show? (Again, please copy and paste the exact output.) Is it possible that you created the tables in mixed case and didn't quote their names when you tried to query them? If so, then you might want to read "Identifiers and Key Words" in the "SQL Syntax" chapter of the documentation. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Referencing created tables fails with message that
On Mon, Feb 28, 2005 at 12:50:25PM +0100, Tommy Svensson wrote:
> - I've used Oracle, DB2, Mimer, and HSQLDB before, and my experience
> with these led me
> to beleive that SQL was case insensitive. In fact, I was so sure of it
> that a case problem
> just never occured to me.
Case isn't a problem if you don't quote identifiers because unquoted
identifiers will be folded to lower case, both when you create them
and then later when you reference them. For example, if you create
a table with this command:
CREATE TABLE XYZ (I INTEGER);
then the system folds XYZ and I to lower case:
\dt
List of relations
Schema | Name | Type | Owner
+--+---+---
public | xyz | table | mfuhr
\d xyz
Table "public.xyz"
Column | Type | Modifiers
+-+---
i | integer |
The following queries should all work (not an all-inclusive list):
SELECT I FROM XYZ;
SELECT i FROM xyz;
SELECT I FROM Xyz;
select i from xyz;
sEleCt i fRoM xYz;
But if you quote identifiers when you create them, then they'll be
created with the exact case you specified and you'll need to quote
them whenever you use them:
CREATE TABLE "XYZ" ("I" INTEGER);
\dt
List of relations
Schema | Name | Type | Owner
+--+---+---
public | XYZ | table | mfuhr
\d "XYZ"
Table "public.XYZ"
Column | Type | Modifiers
+-+---
I | integer |
SELECT "I" FROM "XYZ"; -- works
SELECT I FROM XYZ; -- fails
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL]
On Mon, Feb 28, 2005 at 03:28:24PM -0800, Mohsen Pahlevanzadeh wrote: > Dears,I need to create an pgsql user in C. Perhaps you're looking for the libpq or ECPG documentation: http://www.postgresql.org/docs/8.0/interactive/libpq.html http://www.postgresql.org/docs/8.0/interactive/ecpg.html > Please guide me A good way to learn about PostgreSQL is to study the documentation. Go through the Tutorial, then at least skim the rest so you'll know what capabilities PostgreSQL has and where you can learn more about them. http://www.postgresql.org/docs/8.0/interactive/tutorial.html http://www.postgresql.org/docs/ -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Is any limitations in PostgreSQL?
On Mon, Feb 28, 2005 at 07:49:54PM -0800, Yu Jie wrote: > 1. What's the restriction of database file? How big it > can be(for exmaple, 10TB?). See "What is the maximum size for a row, a table, and a database?" in the FAQ: http://www.postgresql.org/files/documentation/faqs/FAQ.html#4.4 > 2. Is there any restriction for the record and the > document? What kind of record and document can be add > to database. See the aforementioned FAQ and the "Data Types" chapter in the documentation: http://www.postgresql.org/docs/8.0/interactive/datatype.html In addition to the built-in types, users can create their own types. > 3. What's the restriction for the table and the > collection? See the aforementioned FAQ. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] cursor already in use error
On Wed, Mar 02, 2005 at 09:21:44AM +0200, Sim Zacks wrote: > PostGreSQL 8.0beta1 That's pretty old. Have you tried 8.0.1? > I have a function that uses a cursor and it is giving me the error: cursor > "crsr" already in use when the parameters I pass in come from another table. > > The function works fine when I call it by itself, such as select > PartNeedsReschedule(100,1) or select * from PartNeedsReschedule(100,1) > but when I try select PartNeedsReschedule(PartID,1) from Parts then I get > the error. Could you post a simple, self-contained example that demonstrates the problem? Please show definitions of Parts and PartNeedsReschedule and some sample data -- enough that somebody could copy what you post into a test database and duplicate the problem. It'll be easier to help if we can see exactly what you're doing. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 3: 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] cursor already in use error
On Wed, Mar 02, 2005 at 11:15:52AM +0200, Sim Zacks wrote: > select name,testcursor(testid) from test; --doesn't work > select name,testcursor(testid) from test where testid=1; -- works (as does > testid=2 or 3) If I add "close crs;" before the function returns, I get this: SELECT name, testcursor(testid) FROM test; name | testcursor --+ Bob | -Comment 1-Comment 2-Comment 3 Mark | -Comment 1 Tom | (3 rows) Is that what you're after? If so, then the problem might simply be that you're not closing the cursor when you're done with it. Will closing it work in the real code? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 3: 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] aggregate functions on massive number of rows
On Wed, Mar 02, 2005 at 12:23:45PM -0500, Todd Kover wrote: > > I have an aggregate function setup (attached) that I'm calling over a > massive amount of data and am running into: > > ERROR: cannot have more than 2^32-1 commands in a transaction > CONTEXT: PL/pgSQL function "float8_jitter_add" line 16 at if > > error. Since I'm able to do count() and avg() over the same values > without this aggregate function, it's theoretically possible. > > Something was making me think that it was the extract() that was doing > it (I used to have 'select extract(milliseconds from v_rtt_in) into > v_rtt' and something leaded me to believe the error was with that), but > I don't recall what that is, so it looks like it's just the if like it > says. Note the following from the PL/pgSQL "Expressions" documentation: All expressions used in PL/pgSQL statements are processed using the server's regular SQL executor. In effect, a query like SELECT expression is executed using the SPI manager. I'd guess that you are indeed hitting the command limit. You might have more luck with one of the other procedural languages (PL/Perl, PL/Tcl, PL/Python, etc.), but I'd consider coding something like this in C if I were using it with so much data. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] getting attribute names, types
On Thu, Mar 03, 2005 at 03:01:10PM +0530, Surabhi Ahuja wrote: > i have to write a program in C++ using libpq. The program > establishes a connection with the desired database. > I have 4 tables in the database. Now i want to get the list of > attribute names, their data types for each of those 4 tables. how > do i do that? You could query the system catalogs or the Information Schema (the latter available in 7.4 and later). Another way would be to query the tables themselves and use the functions described under "Retrieving Query Result Information" in the libpq documentation. http://www.postgresql.org/docs/8.0/interactive/catalogs.html http://www.postgresql.org/docs/8.0/interactive/information-schema.html http://www.postgresql.org/docs/8.0/interactive/libpq-exec.html#LIBPQ-EXEC-SELECT-INFO -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Pg 8.01 big trouble with LIMIT (bug !?)
On Thu, Feb 24, 2005 at 04:20:03PM -0500, Bruce Momjian wrote: > > My guess is that you have not ANALYZEd the tables recently and the > optimizer is making a bad choice. I think this problem is similar to one a couple of months ago: http://archives.postgresql.org/pgsql-bugs/2005-01/msg00174.php -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] LIMIT and him usage
On Mon, Mar 07, 2005 at 11:31:06AM +0100, Ladislav Linhart wrote: > I have a question. How is using LIMIT in SELECT on the application layer. > Create temprorary table before applyes LIMIT ? Exists any way for don't > create temporary table with all records ? Could you describe what you're trying to do? What problem are you trying to solve? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] number of rows in a cursor
On Mon, Mar 07, 2005 at 05:06:19PM +0100, FERREIRA William (COFRAMI) wrote: > is it possible to know the number of rows into a cursor ? does any function > exists ? Not without scanning the result. See previous discussion: http://archives.postgresql.org/pgsql-general/2005-01/msg01264.php http://archives.postgresql.org/pgsql-general/2005-01/msg01312.php -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Move cursor
On Tue, Mar 08, 2005 at 04:23:37PM +0100, FERREIRA William (COFRAMI) wrote: > i need to use the MOVE function on a cursor, for extracting special rows. > the function is written in pl/pgSQL I don't think PL/pgSQL implements cursors fully. In particular, I don't see MOVE in the PL/pgSQL parser; I don't know if that's intentional or an oversight. Maybe one of the developers will comment. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Move cursor
On Tue, Mar 08, 2005 at 12:16:28PM -0500, Tom Lane wrote: > Michael Fuhr <[EMAIL PROTECTED]> writes: > > I don't think PL/pgSQL implements cursors fully. > > Its cursor facility is certainly far weaker than what's presently in the > main SQL language. I think this is at least partly historical accident > (ie we upgraded the main language and forgot about plpgsql). Would adding MOVE to PL/pgSQL be a fairly trivial effort? If so then I'd consider having a go at it myself, unless it's something somebody else could bang out in five minutes (and was willing to do so). -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL still for Linux only?
On Wed, Mar 09, 2005 at 03:24:05AM +, Tope Akinniyi wrote: > I am wondering at this display of extreme Linux mentality being > displayed by the 'top bras' of the PostgreSQL community. And I > ask, are we encouraging Windows use of PostgreSQL at all? I don't see the "extreme Linux mentality" you mention, and in any case maybe you mean "Unix mentality," where "Unix" refers to a class of operating systems that includes but isn't limited to Linux. Subjects like "PostgreSQL still for Linux only?" are (mis)leading because PostgreSQL runs perfectly well on other Unix-like systems such as FreeBSD, NetBSD, OpenBSD, Solaris, and many others, to judge from directories like src/template in the source code. Linux is popular, but it's NOT the only Unix-like operating system around. Unfortunately the public is coming to equate the two, with the word "Unix" often prompting the question, "You mean Linux?" I will concede that a lot of tools in general tend to be written for Linux, sometimes without regard to whether they'll work even on other Unix-like operating systems. But the PostgreSQL project itself appears to care about portability, so the question "Still for Linux only?" should really be directed at the third-party software that some people find useful. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Problem with inherited table, can you help?...
On Thu, Mar 10, 2005 at 01:31:21AM -0800, Net Virtual Mailing Lists wrote: > 1. set search_path=jl_site2,public; > 2. BEGIN; > 3. INSERT INTO locations (user_id, city, state_id, zip, country_id, > loc_type, deleted, entered_dt) VALUES (37613, 'Glendale', 3, '85301', 1, > 'secondary', 'f', now()); > > .. at this point connection #2 is blocked until I either commit or > rollback the in-process transaction in connection As you guessed, connection #2 is blocked because of a concurrent transaction inserting another record with the same country_id foreign key. PostgreSQL acquires a row-level lock on the referenced key to ensure that it doesn't change while the referencing transaction remains open. Unfortunately it's an exclusive lock, which causes other transactions to block when they try to lock the same row. > More importantly what I can do about this. The countries/states table are > basically static and won't change, but I want the constraint check in > place because it just seems like a good practice. Constraints are indeed good practice. This has come up before, and one possibility is to make the foreign key constraint deferrable and defer its integrity checks so they aren't made until commit time. This has problems of its own, however: you won't detect referential integrity violations until the transaction commits, so you won't get an error for the specific statement that caused the violation. Some have suggested that PostgreSQL should use a weaker lock on the referenced key, but that hasn't been implemented yet. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] error codes in log file?
On Thu, Mar 10, 2005 at 01:58:52PM -0500, David Parker wrote: > Is there a postgresql.conf directive (7.4.5) which will result in error > codes being emitted in the log file? I am thinking of the error codes > in: > http://www.postgresql.org/docs/7.4/interactive/errcodes-appendix.html. You could set log_error_verbosity to 'verbose', either cluster-wide in postgresql.conf, for a particular database with ALTER DATABASE, for a particular user with ALTER USER, or just for a particular session with SET. Verbose logging might show more than you want, but it'll show the error codes. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 3: 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] keeping track of when a row was last modified
On Thu, Mar 10, 2005 at 09:15:28PM +, Sally Sally wrote: > Does postgres automatically keep track of when a row was last modified? No, but you can set up a trigger to do it. The "Trigger Procedures" section of the PL/pgSQL chapter in the documentation has an example: http://www.postgresql.org/docs/8.0/interactive/plpgsql-trigger.html -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] MS Access to PostgreSQL
On Fri, Mar 11, 2005 at 05:19:35PM +, William Shatner wrote: > I have recently migrated from MS Access to PostgreSQL.Previously I had > a SQL command > > ResultSet aGroupResultSet = aGroupPathStmt.executeQuery( > "SELECT \"groupID\",\"fullpath\" FROM \"groups\" WHERE > \"fullpath\" Like '" + > aPath + "'"); > > where aPath was equal to 'folder\another folder\%'. > > The field to be edited stores the full path in the format > 'folder\folder1\folder2' and so on... > The purpose being to change all groups at this level of the > hieracarchy and below, this was achieved using the '%' in Access, this > however doesn't seem to work in PostgreSQL, it doesn't error out but > it just seems to see the '%' as a normal character. You're running into problems with \ being the escape character in string constants and again in patterns. There's some discussion of this in the "Pattern Matching" section of the "Functions and Operators" chapter in the documentation: http://www.postgresql.org/docs/8.0/interactive/functions-matching.html Here are some ways to make it work (dollar quoting available only in 8.0 and later): fullpath LIKE 'folderanother folder%' fullpath LIKE 'folder\\another folder\\%' ESCAPE '' fullpath LIKE $$folder\\another folder\\%$$ fullpath LIKE $$folder\another folder\%$$ ESCAPE '' -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Unique Indexes
On Fri, Mar 11, 2005 at 04:21:49PM +, ILove TheSpam wrote: > My problem is that I want to: > a) Be able to detect when an error occurs and be able to read that error > b) Find out the FieldID for the row which already has the value in it. So > if I try to enter 'blah' into FieldValue and it's already in the row with > FieldID=3, I want the value 3 to be returned to me. I realise this can be > done using a SELECT after the error, but I'd like to avoid that. I'm hoping > Postgres itself has some way of detecting this sort of thing. Could you describe the problem you're trying to solve? It might be easier to help if we knew the ultimate purpose of what you're trying to do. Something like "I want to do this because" -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
