R: [GENERAL] Postgres 8.3-dev
I am trying to install the 8.3-dev version on a Vmware virtual machine with WinXP SP2. I am able to install the 8.2.4.1 version with no problem using the very same settings for both servers as follow: SETTINGS : Account name postgres with password postmaster Accept connections on all addresses, not just localhost Install Adminpack During 8.3-dev installation I got through the usual error, and there are no previous warning during the installation process. MSG : Failed to run initdb: 1! Please see the logfile in 'C:\...\...\initdb.log'. Note! You must read . LOG FILE : The same as the other email I then reset the virtual machine and installed the 8.2 with no problem. At that point I tried to install the 8.3-dev with the account created by the 8.2 installation and I end up the same error. Paolo -Messaggio originale- Da: Magnus Hagander [mailto:[EMAIL PROTECTED] Inviato: venerdì 4 maggio 2007 7.58 A: [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Oggetto: Re: [GENERAL] Postgres 8.3-dev > I am trying out postgresql-8.3-dev1 on Windows XP SP2 and during the installation, I get the following error during the database cluster initialization : This looks pretty interesting. Are you specifying an existing account for the service or are you letting the installer create one? Any warnings or other messages earlier in the install process? Are you able to install 8.2 on the same machine with the same options and account? /Magnus > The files belonging to this database system will be owned by user "SYSTEM". ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: R: [GENERAL] Postgres 8.3-dev
On Fri, May 04, 2007 at 09:00:32AM +0200, Paolo Saudin wrote: > I am trying to install the 8.3-dev version on a Vmware virtual machine with > WinXP SP2. I am able to install the 8.2.4.1 version with no problem using > the very same settings for both servers as follow: There is no 8.2.4.1 version. There is 8.2.4 or 8.2.1. or are you using EnterpriseDB and not PostgreSQL? IIRC, the installer is differnt there... > SETTINGS : > Account name postgres with password postmaster Is this both for the service account and the superuser account? Does this accoutn already exist, or is the installer creating it? > I then reset the virtual machine and installed the 8.2 with no problem. At > that point I tried to install the 8.3-dev with the account created by the > 8.2 installation and I end up the same error. Any ideas on this Dave? //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Query not using index despite high statistics
Hello list, I have a SELECT query that uses Seq scans instead of index scan despite that the index scan is faster. Below is the query and its first run with enable seqsan = true which give a Seq Scan on tbl_structure (cost=0.00..19147.29 rows=172229 width=97) (actual time=0.094..878.309 rows=172229 loops=1). Total query time 24116ms. After that I turn off seqscans and now query time is 1257ms. I've tried raising the statistics on pk_structure_id and fk_structure_id but to no avail. Regards, henrik === SETTING ENABLE_SEQSCAN = TRUE; = set enable_seqscan = true; explain analyze SELECT COUNT(*) FROM tbl_file_structure JOIN tbl_file ON pk_file_id = fk_file_id JOIN tbl_structure ON pk_structure_id = fk_structure_id JOIN tbl_archive ON pk_archive_id = fk_archive_id JOIN tbl_share ON pk_share_id = fk_share_id JOIN tbl_computer ON pk_computer_id = fk_computer_id JOIN tbl_filetype ON pk_filetype_id = fk_filetype_id JOIN tbl_acl ON fk_file_structure_id = pk_file_structure_id WHERE ( pk_file_id IN (1595776,1595774,1595773,1595779,1615586,1595777,1595778,1614443,1695319 , 1695955,1695956,1587155,1695324,1616597,1614228,1695320,1695639,1696142, 1603869,1696202,1695848,1696203,1695412,1695561,1695562,1695563,1657822, 1694281,1693841,1585629,1696143,1694280,1693742,1694117,1589318,1695925, 1695849,1614442,1695584,1695540,1695541,1695542,1695539,1695691,1615887, 1615888,1695694,1695695,1696008,1659029,1694889,1695636,1695637,1695635, 1695633,1695634,1615737,1694888,1615886,1615889,1695536,1695693,1693814, 1693795,1695986,1657902,1696000,1603090,1611660,1696990,1585946,1696041, 1696042,1695261,1648536,1648539,1695850,1695869,1695873,1695871,1696328, 1696330,1696332,1589191,1696329,1589284,1696331,1696053,1696063,1696187, 1640756,1641544,1641695,1695985,1695344,1695415,1695531,1695594,1609809, 1695405,1615898,1695432,1695435,1695851,1692218,1658028,1695923,1695924, 1696054,1695365,1695433,1695436,1696882,1696229,1696230,1696231,1695544, 1658218,1694239,1693510,1697990,1615868,1695417,1598456,1695841,1696222, 1696087,1695604,1615864,1615860,1695762,1696045,1695874,1609716,1695631, 1695687,1695817,1615731,1615751,1695554,169,1695556,1695557,1695558, 1589560,1696223,1658096,1694048,1698519,1696064,1695380,1695518,1695434, 1696043,1696044,1695469,1695870,1695872,1696188,1695404,1695403,1695499, 1695842,1693566,1693375,1595775,1694879,1654126,1654134,1654128,1654124, 1654129,1654135,1654125,1654131,1654123,1654127,1696144,1694891,1695571, 1694887,1603749,1589686,1695407,1585638,1695449,1695524,1695523,1695533, 1654122,1654130,1657476,1658392,1693991,1596090,1596150,1690944,1690945, 1701473,1701817,1701914,1654133,1654076,1695625,1615725,1615920,1654132, 1654077,1654072,1693719,1654606,1692783,1694150,1596142,1654529,1696311, 1616047,1654409,1657157,1697755,1698044,1600214,1654136,1697019,1659100, 1694049,1591073,1698564,1694546,1694226,1693972,1693779,1658309,1698426, 1692830,1693894,1657308,1697795,1618611,1590802,1654620,1658097,1692757, 1697780,1698520,1693815,1693796,1655314,1655323,1694612,1693261,1697622, 1698008,1698302,1654668,1697770,1657198,1657229,1657244,1657286,1655797, 1694343,1697446,1600483,1655894,1603845,1696786,1695304)) AND archive_complete = true GROUP BY file_name, file_ctime, structure_path, pk_computer_id, filetype_icon, computer_name, share_name, share_path; "GroupAggregate (cost=115540.66..115968.69 rows=11414 width=148) (actual time=2172.513..2412.807 rows=297 loops=1)" " -> Sort (cost=115540.66..115569.20 rows=11414 width=148) (actual time=2171.525..2340.522 rows=14769 loops=1)" "Sort Key: tbl_file.file_name, tbl_file.file_ctime, tbl_structure.structure_path, tbl_computer.pk_computer_id, tbl_filetype.filetype_icon, tbl_computer.computer_name, tbl_share.share_name, tbl_share.share_path" "-> Nested Loop (cost=17179.08..113910.45 rows=11414 width=148) (actual time=104.599..1653.992 rows=14769 loops=1)" " -> Hash Join (cost=17179.08..37262.58 rows=3161 width=156) (actual time=104.440..1404.388 rows=2787 loops=1)" "Hash Cond: (tbl_structure.fk_archive_id = tbl_archive.pk_archive_id)" "-> Hash Join (cost=17056.35..37096.40 rows=3161 width=130) (actual time=103.952..1386.744 rows=2787 loops=1)" "
Re: R: [GENERAL] Postgres 8.3-dev
Magnus Hagander wrote: On Fri, May 04, 2007 at 09:00:32AM +0200, Paolo Saudin wrote: I am trying to install the 8.3-dev version on a Vmware virtual machine with WinXP SP2. I am able to install the 8.2.4.1 version with no problem using the very same settings for both servers as follow: There is no 8.2.4.1 version. There is 8.2.4 or 8.2.1. or are you using EnterpriseDB and not PostgreSQL? IIRC, the installer is differnt there... I suspect he means 8.2.4-1 which is how the archive is named in case it needs re-rolling. SETTINGS : Account name postgres with password postmaster Is this both for the service account and the superuser account? Does this accoutn already exist, or is the installer creating it? I then reset the virtual machine and installed the 8.2 with no problem. At that point I tried to install the 8.3-dev with the account created by the 8.2 installation and I end up the same error. Any ideas on this Dave? The error in the log is in the create conversions phase of initdb, so I doubt it's an installer issue. I don't have time to look right now, but does initdb do anything unusual there? I've got a sneaking suspicion I've seen a failure at this point before... Regards, Dave. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: R: [GENERAL] Postgres 8.3-dev
On Fri, May 04, 2007 at 09:38:48AM +0100, Dave Page wrote: > Magnus Hagander wrote: > >On Fri, May 04, 2007 at 09:00:32AM +0200, Paolo Saudin wrote: > >>I am trying to install the 8.3-dev version on a Vmware virtual machine > >>with > >>WinXP SP2. I am able to install the 8.2.4.1 version with no problem using > >>the very same settings for both servers as follow: > > > >There is no 8.2.4.1 version. There is 8.2.4 or 8.2.1. or are you using > >EnterpriseDB and not PostgreSQL? IIRC, the installer is differnt there... > > I suspect he means 8.2.4-1 which is how the archive is named in case it > needs re-rolling. Oh. Sorry about that. Too early in the morning (I know it wasn't particularly early, but obviously too early) > >>SETTINGS : > >>Account name postgres with password postmaster > > > >Is this both for the service account and the superuser account? Does this > >accoutn already exist, or is the installer creating it? > > > >>I then reset the virtual machine and installed the 8.2 with no problem. At > >>that point I tried to install the 8.3-dev with the account created by the > >>8.2 installation and I end up the same error. > > > >Any ideas on this Dave? > > The error in the log is in the create conversions phase of initdb, so I > doubt it's an installer issue. I don't have time to look right now, but > does initdb do anything unusual there? I've got a sneaking suspicion > I've seen a failure at this point before... Yeah. But look at the part about SYSTEM being the owner, I wonder if that's related. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Have I b0rked something? Slow comparisons on "where x in (...)"
Listmail wrote: > > Followup to my previous test, with an index this time > > EXPLAIN ANALYZE SELECT * FROM test WHERE value IN ( 1000 integers ) I'm not quite sure what you're trying to measure here, but I don't think it is what was suggested. IIRC the suggestion was to move the values from your IN (...) operator into a temp table and join against that. Try measuring something like this: EXPLAIN ANALYZE SELECT * FROM table JOIN test ON (table.column = test.value) vs. EXPLAIN ANALYZE SELECT * FROM table WHERE value IN ( 1000 integers ) -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] cant get pg_dump/pg_restore to behave
On 5/4/07, Tom Lane <[EMAIL PROTECTED]> wrote: There are several obvious things wrong with that (eg, psql cannot read -Fc format dumps) so I suppose it's an editorialization on what you really typed. right, what i posted was a typo, what i ran did not have the -Fc Perhaps the problem is hidden there. Can you show us an *exact* transcript of a failing session? [EMAIL PROTECTED] 0 ~]$ psql -q postgres=# DROP DATABASE gforge5; postgres=# CREATE DATABASE gforge5 WITH TEMPLATE = template0 ENCODING = 'UTF8'; postgres=# [EMAIL PROTECTED] 0 ~]$ psql -d gforge5 -f gforge.schema SET SET SET COMMENT CREATE LANGUAGE SET psql:gforge.schema:31: ERROR: could not access file "$libdir/tsearch2": No such file or directory psql:gforge.schema:34: ERROR: function public.gtsvector_in(cstring) does not exist psql:gforge.schema:42: ERROR: type gtsvector does not exist ... -mike ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] cant get pg_dump/pg_restore to behave
On 5/4/07, Tom Lane <[EMAIL PROTECTED]> wrote: Well, I loaded and dumped and reloaded this schema in 8.1 without any problem, so I'm still baffled. oh, and the machine that i created the dump on and the machine i loaded the dump on are both Fedora Core 6 that report: $ postgres --version postgres (PostgreSQL) 8.1.8 -mike ---(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
R: R: [GENERAL] Postgres 8.3-dev
> There is no 8.2.4.1 version. There is 8.2.4 or 8.2.1. or are you using > EnterpriseDB and not PostgreSQL? IIRC, the installer is differnt there... Sorry, the version is 8.2.4, the latest available on the PostgreSQL web site and I am using only PostgreSQL. > Is this both for the service account and the superuser account? Does this > accoutn already exist, or is the installer creating it? I installed both versions with the following settings : service account: postgres with password postmaster superuser account : postgres with password postgres When I install the 8.3-dev version on a clean machine, the installer creates the account When I install the 8.3-dev version after installing the 8.2.4, the installer uses the already created account (postgres with password postmaster ) HERE IS THE LOG FROM THE WINDOWS SYSTEM EVENT LOG Tipo evento:Errore Origine evento: PostgreSQL Categoria evento: Nessuno ID evento: 0 Data: 04/05/2007 Ora:10.57.14 Utente: N/D Computer: XPHOME Descrizione: Impossibile trovare la descrizione dell'ID evento ( 0 ) nell'origine ( PostgreSQL ). Il computer locale potrebbe non disporre delle necessarie informazioni nel Registro di sistema o dei file DLL necessari per visualizzare messaggi da un computer remoto. Utilizzare /AUXSOURCE= flag per recuperare la descrizione. Per ulteriori informazioni, consultare la Guida in linea e supporto tecnico. Le seguenti informazioni sono parte dell'evento: FATAL: could not load library "C:/Programmi/PostgreSQL/8.3-dev1/lib/ascii_and_mic.dll": unknown error 14001 STATEMENT: CREATE OR REPLACE FUNCTION ascii_to_mic (INTEGER, INTEGER, CSTRING, INTERNAL, INTEGER) RETURNS VOID AS '$libdir/ascii_and_mic', 'ascii_to_mic' LANGUAGE C STRICT; . Paolo -Messaggio originale- Da: Magnus Hagander [mailto:[EMAIL PROTECTED] Inviato: venerdì 4 maggio 2007 9.39 A: Paolo Saudin Cc: pgsql-general@postgresql.org Oggetto: Re: R: [GENERAL] Postgres 8.3-dev On Fri, May 04, 2007 at 09:00:32AM +0200, Paolo Saudin wrote: > I am trying to install the 8.3-dev version on a Vmware virtual machine with > WinXP SP2. I am able to install the 8.2.4.1 version with no problem using > the very same settings for both servers as follow: There is no 8.2.4.1 version. There is 8.2.4 or 8.2.1. or are you using EnterpriseDB and not PostgreSQL? IIRC, the installer is differnt there... > SETTINGS : > Account name postgres with password postmaster Is this both for the service account and the superuser account? Does this accoutn already exist, or is the installer creating it? > I then reset the virtual machine and installed the 8.2 with no problem. At > that point I tried to install the 8.3-dev with the account created by the > 8.2 installation and I end up the same error. Any ideas on this Dave? //Magnus ---(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] Have I b0rked something? Slow comparisons on "where x in (...)"
I used VALUES as a replacement for the temporary table since for this application, it is a lot more useful. The point is : SELECT * FROM table WHERE value IN ( 1000 integers ) : does 1000 comparisons for each row SELECT * FROM table WHERE value IN ( VALUES (1000 integerss) ) : builds a Hash with the 1000 values and uses it to test rows, which is a lot faster if you have many values to compare with. The first one is faster if the number of values in the IN() is small. The second one is faster if the number of values in the IN() is large. EXPLAIN ANALYZE SELECT * FROM table JOIN test ON (table.column = test.value) It wouldn't give the same result : both queries above remove duplicates, this one does not. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Permission denied to create database
Sleep deprived and surely doing something stupid here; I can't seem to
confer the ability to create databases on a regular user. I always get
"permission denied to create database". One note: template1 has had
some C functions added to it. Could that be related to the problem?
$ createdb -U joe joejunkdb
createdb: database creation failed: ERROR: permission denied to create
database
$ sudo -u postgres psql -c "grant all on tablespace pg_default to joe"
Password:
GRANT
$ createdb -U joe joejunkdb
createdb: database creation failed: ERROR: permission denied to create
database
joe=> \db+
List of tablespaces
Name| Owner | Location | Access
privileges| Description
+--+--++-
pg_default | postgres | | {postgres=C/postgres,joe=C/postgres} |
pg_global | postgres |
||
joe=> \l+
List of databases
Name| Owner | Encoding | Tablespace |Description
---+--+--++---
...
template1 | postgres | UTF8 | pg_default | Default template database
For some reason, I haven't succeeded by reading the docs and googling.
Thanks for jarring my memory,
Kevin Murphy
PostgreSQL 8.2.4
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Permission denied to create database
Kevin Murphy wrote: Sleep deprived and surely doing something stupid here; I can't seem to confer the ability to create databases on a regular user. I always get "permission denied to create database". One note: template1 has had some C functions added to it. Could that be related to the problem? $ createdb -U joe joejunkdb createdb: database creation failed: ERROR: permission denied to create database $ sudo -u postgres psql -c "grant all on tablespace pg_default to joe" Password: GRANT $ createdb -U joe joejunkdb createdb: database creation failed: ERROR: permission denied to create database As a superuser: ALTER USER joe CREATEDB Then go get some sleep :-) -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Permission denied to create database
Can u tell us what are the role privilages granted to user "joe"
May be you r missing with the create database privilage to user joe
With Regards
Ashish
- Original Message -
From: "Kevin Murphy" <[EMAIL PROTECTED]>
To:
Sent: Friday, May 04, 2007 6:36 PM
Subject: [GENERAL] Permission denied to create database
Sleep deprived and surely doing something stupid here; I can't seem to
confer the ability to create databases on a regular user. I always get
"permission denied to create database". One note: template1 has had some
C functions added to it. Could that be related to the problem?
$ createdb -U joe joejunkdb
createdb: database creation failed: ERROR: permission denied to create
database
$ sudo -u postgres psql -c "grant all on tablespace pg_default to joe"
Password:
GRANT
$ createdb -U joe joejunkdb
createdb: database creation failed: ERROR: permission denied to create
database
joe=> \db+
List of tablespaces
Name| Owner | Location | Access privileges
| Description
+--+--++-
pg_default | postgres | | {postgres=C/postgres,joe=C/postgres} |
pg_global | postgres | |
|
joe=> \l+
List of databases
Name| Owner | Encoding | Tablespace |
Description ---+--+--++---
...
template1 | postgres | UTF8 | pg_default | Default template database
For some reason, I haven't succeeded by reading the docs and googling.
Thanks for jarring my memory,
Kevin Murphy
PostgreSQL 8.2.4
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] cant get pg_dump/pg_restore to behave
"Mike Frysinger" <[EMAIL PROTECTED]> writes: > [EMAIL PROTECTED] 0 ~]$ psql -d gforge5 -f gforge.schema > ... > psql:gforge.schema:31: ERROR: could not access file > "$libdir/tsearch2": No such file or directory You don't have tsearch2 installed in the new installation. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] multi-language web application: is it possible?
I have many tables like the table Person:below, in mysql database. person_id, first_name,last_name, mi, gb_first_name, gb_last_name, b5_first_name, b5_last_name, gender, dob where different columns storing strings in different encodings. At anytime, a web user can switch the language and the application will get the values in the right columns to generate web pages. The purpose of Multi-language tables is to make multilanguage dynamic content management easier for web applications. For example, to add a person record, the user enter the English name, then switch the session language to gb2312, enter the Chinese name in gb2312, and then switch the session language to big5, enter the name in big5. And then commit the data into the database. The whole thing sounds complicated but can be treated as a pattern and let a framework to take care of those and the code can be as clean as a single language app. I actually have the framework that works well for me with mysql database. I'm trying to port the app to pgsql database but got trouble doing so. I can read and set a row with multiple languages (in some paricular cases), but cannot do queries like (gb_first_name = 'A' and b5_first_name = 'B') with A a gb2312 string, B a big5 string. The tables are of unicode encoded, and the dbclient encoding is set to GBK. The application's char set are selectable by user among iso-8859-1,gb2312 and big5. I didn't do anything about language encoding in mysql database, it just worked for me. At least with english, gb2312 and big5 altogether in a table like table Person above. I noticed that (english, gb2312, big5, Jp) cannot work together even in mysql database. My approach seems fine with most western languages So after all such experimental work, I still don't know how to make a real multi language web app such that the languages are switchable within the same session. Any suggestions? Any web application known to be able to solve the problem? Thanks
Re: R: [GENERAL] Postgres 8.3-dev
Dave Page <[EMAIL PROTECTED]> writes: > The error in the log is in the create conversions phase of initdb, so I > doubt it's an installer issue. I don't have time to look right now, but > does initdb do anything unusual there? I've got a sneaking suspicion > I've seen a failure at this point before... I believe that's the first step that needs to load a shared library (ie, the ones containing the conversion functions), so I'd bet on some sort of path mistake, or a file-permissions problem on the library dlls. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] multi-language web application: is it possible?
Elim Qiu schrieb: > I have many tables like the table Person:below, in mysql database. > > person_id, first_name,last_name, mi, gb_first_name, gb_last_name, > b5_first_name, b5_last_name, gender, dob > > where different columns storing strings in different encodings. At > anytime, a web user can switch the language and the application will get > the values in the right columns to generate web pages. The purpose of > Multi-language tables is to make multilanguage dynamic content > management easier for web applications. For example, to add a person > record, the user enter the English name, then switch the session > language to gb2312, enter the Chinese name in gb2312, and then switch > the session language to big5, enter the name in big5. And then commit > the data into the database. The whole thing sounds complicated but can > be treated as a pattern and let a framework to take care of those and > the code can be as clean as a single language app. I actually have the > framework that works well for me with mysql database. It would be easier to normalize the tables and have a referral. Also person names arent usually localized - at least this would be very uncommon. If you want you can transcribe them when you output it (e.g. replace umlauts with their dual char ascii representation) > I'm trying to port the app to pgsql database but got trouble doing so. I > can read and set a row with multiple languages (in some paricular > cases), but cannot do queries like (gb_first_name = 'A' and > b5_first_name = 'B') with A a gb2312 string, B a big5 string. The tables > are of unicode encoded, and the dbclient encoding is set to GBK. The > application's char set are selectable by user among iso-8859-1,gb2312 > and big5. the language and their encoding are more or less orthogonal. So if you settle for one common encoding which covers all the language you want to use, then its easy to have all the language words side by side. General unicode (in its utf-8 representation) is used in postgres for such a general case. > I didn't do anything about language encoding in mysql database, it just > worked for me. At least with english, gb2312 and big5 altogether in a > table like table Person above. I noticed that (english, gb2312, big5, > Jp) cannot work together even in mysql database. My approach seems fine > with most western languages Well it might work but it might not work as expected. Beside the encoding, you also have a collating order to obey. This is very depending on the language (and the portion of the charset it uses) so this would not even work in mySQL or other databases w/o any specification. In PG, assuming you would have a table with the texts, it would carry the language_id too and you could provide a functional index which would take care of the sorting of the individual language based on the language id. > So after all such experimental work, I still don't know how to make a > real multi language web app such that the languages are switchable > within the same session. I'd highly recommend restructuring the tables to be more flexible and to have a faster and cleaner approach. Joins do not hurt so much in postgres so use them to your advantage. Regards Tino ---(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] Stored procedure
Hi,
thank you for your detailled answer!
Today I had the possibility to test it in the office. The procedure
could be stored.
But when I call it SELECT create_geom_table('testtable') Then an error
occurs: column testtable not available. Do you know why?
Regards
Hakan Kocaman schrieb:
Hi,
your example should look like this:
CREATE OR REPLACE FUNCTION create_geom_table(table_name text) RETURNS void AS
$BODY$
DECLARE
func_text text;
BEGIN
func_text:='DROP TABLE ' || table_name ||';
CREATE TABLE ' || table_name ||'
(
id integer,
mytimestamp timestamp without time zone--,
--geom geometry,
--CONSTRAINT enforce_dims_geom CHECK (ndims(geom) = 2),
--CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = '|| quote_literal('MULTIPOLYGON') ||'::text OR geom IS NULL),
--CONSTRAINT enforce_srid_geom CHECK (srid(geom) = -1)
)
WITHOUT OIDS;
ALTER TABLE ' || quote_literal(table_name) ||'OWNER TO "admin";
--CREATE INDEX geo_index ON '|| quote_literal(table_name) ||'USING gist(geom);
--ALTER FUNCTION create_geom_table('|| quote_literal(table_name) ||') OWNER TO "admin";
';
EXECUTE func_text;
END;
$BODY$ LANGUAGE plpgsql;
select create_geom_table('test_geom_tbl');
It's not exactly the same, hence i don't got some of yout types(geom for
example) laying around, but you get the picture, no?
Best regards
Hakan Kocaman
Software-Development
digame.de GmbH
Richard-Byrd-Str. 4-8
50829 Köln
Tel.: +49 (0) 221 59 68 88 31
Fax: +49 (0) 221 59 68 88 98
Email: [EMAIL PROTECTED]
digame.de GmbH, Sitz der Gesellschaft: Köln, Handelsregister Köln, HRB 32349
Geschäftsführung: Werner Klötsch, Marco de Gast
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
Thorsten Kraus
Sent: Thursday, May 03, 2007 5:27 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Stored procedure
Hi,
thanks for your answer, but I don't get the point. Perhaps
you can give
me a small example how to get the EXECUTE into a stored procedure.
Regards
Hakan Kocaman schrieb:
Hi,
Try EXECUTE
http://www.postgresql.org/docs/8.2/interactive/plpgsql-stateme
nts.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
Best Regards
Hakan Kocaman
Software-Development
digame.de GmbH
Richard-Byrd-Str. 4-8
50829 Köln
Tel.: +49 (0) 221 59 68 88 31
Fax: +49 (0) 221 59 68 88 98
Email: [EMAIL PROTECTED]
digame.de GmbH, Sitz der Gesellschaft: Köln,
Handelsregister Köln, HRB 32349
Geschäftsführung: Werner Klötsch, Marco de Gast
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
Thorsten Kraus
Sent: Thursday, May 03, 2007 5:00 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Stored procedure
Hi NG,
I want to write a stored procedure which creates a
table in my PostgreSQL database. The procedure has one input
parameter: the table name.
Here is my first try, but that does not work:
--
CREATE OR REPLACE FUNCTION create_geom_table(text)
RETURNS void AS $$
DECLARE
--table_name TEXT;
BEGIN
---
CREATE TABLE table_name
(
id integer,
"time" timestamp without time zone,
geom geometry,
CONSTRAINT enforce_dims_geom CHECK (ndims(geom) = 2),
CONSTRAINT enforce_geotype_geom CHECK
(geometrytype(geom) = 'MULTIPOLYGON'::text OR geom IS NULL),
CONSTRAINT enforce_srid_geom CHECK (srid(geom) = -1)
)
WITHOUT OIDS;
ALTER TABLE table_name OWNER TO "admin";
CREATE INDEX geo_index ON table_name USING gist(geom);
---
ALTER FUNCTION create_geom_table(table_name) OWNER TO "admin";
END;
$$ LANGUAGE plpgsql;
--
Can someone tell me what's wrong with this and what I
have to change?
Regards,
Thorsten
---(end of
broadcast)---
TIP 5: don't forget to increase your free space map settings
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Permission denied to create database
> > $ sudo -u postgres psql -c "grant all on tablespace pg_default to joe" > Password: > GRANT > > $ createdb -U joe joejunkdb > createdb: database creation failed: ERROR: permission denied to create > database > How about ALTER ROLE joe CREATEDB Regards MP ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Stored procedure
Hi,
could you please post the complete code that you used to create the function.
It sounds suspicously, that pg thinks 'testtable' is a coloum.
Have you set proper quotes in your function-code?
Maybe i got some mistakes regarding the usage of quote_literal in my sample
code.
Till later
Hakan Kocaman
Software-Development
digame.de GmbH
Richard-Byrd-Str. 4-8
50829 Köln
Tel.: +49 (0) 221 59 68 88 31
Fax: +49 (0) 221 59 68 88 98
Email: [EMAIL PROTECTED]
digame.de GmbH, Sitz der Gesellschaft: Köln, Handelsregister Köln, HRB 32349
Geschäftsführung: Werner Klötsch, Marco de Gast
From: Thorsten Kraus [mailto:[EMAIL PROTECTED]
Sent: Friday, May 04, 2007 5:36 PM
To: Hakan Kocaman; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Stored procedure
Hi,
thank you for your detailled answer!
Today I had the possibility to test it in the office. The procedure
could be stored.
But when I call it SELECT create_geom_table('testtable') Then an error
occurs: column testtable not available. Do you know why?
Regards
Hakan Kocaman schrieb:
Hi,
your example should look like this:
CREATE OR REPLACE FUNCTION create_geom_table(table_name text)
RETURNS void AS $BODY$
DECLARE
func_text text;
BEGIN
func_text:='DROP TABLE ' || table_name ||';
CREATE TABLE ' || table_name ||'
(
id integer,
mytimestamp timestamp without time zone--,
--geom geometry,
--CONSTRAINT enforce_dims_geom CHECK
(ndims(geom) = 2),
--CONSTRAINT enforce_geotype_geom CHECK
(geometrytype(geom) = '|| quote_literal('MULTIPOLYGON') ||'::text OR geom IS
NULL),
--CONSTRAINT enforce_srid_geom CHECK
(srid(geom) = -1)
)
WITHOUT OIDS;
ALTER TABLE ' || quote_literal(table_name)
||'OWNER TO "admin";
--CREATE INDEX geo_index ON '||
quote_literal(table_name) ||'USING gist(geom);
--ALTER FUNCTION create_geom_table('||
quote_literal(table_name) ||') OWNER TO "admin";
';
EXECUTE func_text;
END;
$BODY$ LANGUAGE plpgsql;
select create_geom_table('test_geom_tbl');
It's not exactly the same, hence i don't got some of yout
types(geom for example) laying around, but you get the picture, no?
Best regards
Hakan Kocaman
Software-Development
digame.de GmbH
Richard-Byrd-Str. 4-8
50829 Köln
Tel.: +49 (0) 221 59 68 88 31
Fax: +49 (0) 221 59 68 88 98
Email: [EMAIL PROTECTED]
digame.de GmbH, Sitz der Gesellschaft: Köln, Handelsregister
Köln, HRB 32349
Geschäftsführung: Werner Klötsch, Marco de Gast
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
Thorsten Kraus
Sent: Thursday, May 03, 2007 5:27 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Stored procedure
Hi,
thanks for your answer, but I don't get the point.
Perhaps
you can give
me a small example how to get the EXECUTE into a stored
procedure.
Regards
Hakan Kocaman schrieb:
Hi,
Try EXECUTE
http://www.postgresql.org/docs/8.2/interactive/plpgsql-stateme
nts.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
Best Regards
Re: [GENERAL] script for taking incremental backup in postgres in LINUX
On Thu, May 03, 2007 at 02:12:12AM -0700, pumesh wrote: > may lost. So what should i do to make the backup continuously or during > these intervals. http://www.postgresql.org/docs/8.2/interactive/continuous-archiving.html ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Permission denied to create database
Richard Huxton wrote: Kevin Murphy wrote: Sleep deprived and surely doing something stupid here; I can't seem to confer the ability to create databases on a regular user. As a superuser: ALTER USER joe CREATEDB Thanks, Richard and others who replied. I don't have to deal with permissions very often. -Kevin ---(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] Query not using index despite high statistics
Henrik Zagerholm <[EMAIL PROTECTED]> writes: > I have a SELECT query that uses Seq scans instead of index scan > despite that the index scan is faster. Try 8.2, it's a bit smarter about the costs of repeated indexscans on the inside of a nestloop. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] cant get pg_dump/pg_restore to behave
On 5/4/07, Tom Lane <[EMAIL PROTECTED]> wrote: "Mike Frysinger" <[EMAIL PROTECTED]> writes: > [EMAIL PROTECTED] 0 ~]$ psql -d gforge5 -f gforge.schema > ... > psql:gforge.schema:31: ERROR: could not access file > "$libdir/tsearch2": No such file or directory You don't have tsearch2 installed in the new installation. looks like it's provided by "postgresql-contrib" ... sorry i guess my unfamiliarity with postgres shows as i didnt know that this "tsearch2" was a postrgres thing installing that package fixes all the errors (except missing gforge role, but that one i can handle) sorry for the protracted thread and thanks for your help :) -mike ---(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] varchar as primary key
On 5/3/07, Alexander Staubo <[EMAIL PROTECTED]> wrote: PostgreSQL uses B-trees for its indexes, insertion time is logarithmic regardless of the type of the key, but strings have a larger overhead since they involve character comparisons; (i - j) is a lot faster than strcmp(i, j). If you do go for strings, I would suggest that the beginning of the key be statistically distributed as widely as possible; ie., avoid common prefixes. I think the performance benefits of i - j over strcmp(i,j) are mostly irrelevant, locale issues aside. The main reason why integer keys can be faster is because the index is smaller and puts less pressure on cache. This has to stacked up against the fact you are often hitting the varchar index anyways for sorting and filtering purposes (swapping a int for text index is only a guaranteed win if you can drop the text index completely). So, by using integers from performance perspective we are mostly trying to prevent a cache miss (during which time a computer might perform 100k strcmp operations). If there is also a varchar index, and it is used for various queries, it may actually be faster to drop the integer index altogether because it is competing with cache resources with the integer index. Unfortunately, this is more often the case than not in my experience. As solid state technologies continue to mature and near zero latency storage systems become widespread, this advantage will lessen as the penalty for a cache miss becomes much less. merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] varchar as primary key
On Thu, 2007-05-03 at 23:08 -0400, Tom Lane wrote: > Jeff Davis <[EMAIL PROTECTED]> writes: > > If you're using a non-C locale, it's slower than strcmp() too. > > PostgreSQL has to do an extra memcpy() in order to use strcoll(), > > because strings in postgresql aren't necessarily NULL-terminated and > > there's no such thing as strncoll(), unfortunately (a comment in the > > code points this out). > > The memcpy is the least of the problem --- in many non-C locales, > strcoll() is simply a dog, because the collation rules are ridiculously > complex. > I was going by: src/backend/utils/adt/varlena.c /* * Unfortunately, there is no strncoll(), so in the non-C locale case we * have to do some memory copying. This turns out to be significantly * slower, so we optimize the case where LC_COLLATE is C. We also try to * optimize relatively-short strings by avoiding palloc/pfree overhead. */ I tried with some simple C code, and it looks like the memcpy() does account for a significant slowdown -- at least in my case (en_US.UTF-8). In my tests, I was just comparing two strings: "abcdefghijklmnop1" "abcdefghijklmnop2" (which seem to be "normal" length for a string that might be sorted) I used strcmp() and strcoll() in a tight loop, and the result was indistinguishable. However, when I added in two memcpy()s -- which are necessary for any non-C locale -- it slowed down drastically (an order of magnitude). I also made two test data directories on my workstation, one C and one UTF-8, and then I made a table in each consisting of 1M records of md5 (random()). The one with locale C took about 9 seconds to sort, and the one with en_US.UTF-8 took about 16 seconds to sort. So, I think for some locales memcpy() is the problem and in others the memcpy() overhead may be overshadowed by strcoll(). When memcpy() is the problem it is disappointing because it would be completely unnecessary if only there existed a strncoll() :( Regards, Jeff Davis ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] varchar as primary key
Jeff Davis <[EMAIL PROTECTED]> writes: > I used strcmp() and strcoll() in a tight loop, and the result was > indistinguishable. That's not particularly credible ... were you testing this in a standalone test program? If so, did you remember to do setlocale() first? Without that, you'll be in C locale regardless of environment contents. regards, tom lane ---(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] varchar as primary key
On Fri, 2007-05-04 at 13:52 -0400, Tom Lane wrote:
> Jeff Davis <[EMAIL PROTECTED]> writes:
> > I used strcmp() and strcoll() in a tight loop, and the result was
> > indistinguishable.
>
> That's not particularly credible ... were you testing this in a
> standalone test program? If so, did you remember to do setlocale()
> first? Without that, you'll be in C locale regardless of environment
> contents.
I have attached a revised cmp.c that includes some extra checks. It
looks like the locale is being set correctly and still I don't see a
difference.
$ gcc --version
gcc (GCC) 3.4.5 20051201 (Red Hat 3.4.5-2)
$ uname -a
_ 2.6.9-34.ELsmp #1 SMP Wed Mar 8 00:27:03 CST 2006 i686
i686 i386 GNU/Linux
$ ./cmp
locale set to: en_US.UTF-8
strcmp time elapsed: 2034183 us
strcoll time elapsed: 2019880 us
If I had to guess, I'd say maybe strcoll() optimizes the simple cases
somehow.
[ checks FreeBSD ... ]
On FreeBSD, it's a different story. strcoll() really hurts there
(painfully so). I'm glad you pointed that out, because I have my
production boxes on FreeBSD.
Regards,
Jeff Davis
#include
#include
#include
#include
#define ITERATIONS 10
#define THE_LOCALE "en_US.UTF-8"
int main(int argc, char *argv[]) {
int i;
char buff11[256];
char buff12[256];
char *buff21;
char *buff22;
char *str1 = "abcdefghijklmnop1";
char *str2 = "abcdefghijklmnop2";
char *newlocale;
struct timeval t1,t2,t3;
int elapsed_strcmp,elapsed_strcoll;
int len1 = strlen(str1);
int len2 = strlen(str2);
if( (newlocale = setlocale(LC_ALL,THE_LOCALE)) == NULL ) {
printf("error setting locale!\n");
exit(1);
}
else {
printf("locale set to: %s\n",newlocale);
}
gettimeofday(&t1,NULL);
for(i=0; i < ITERATIONS; i++) {
strcmp(str1,str2);
}
gettimeofday(&t2,NULL);
for(i=0; i < ITERATIONS; i++) {
strcoll(str1,str2);
}
gettimeofday(&t3,NULL);
elapsed_strcmp = (t2.tv_sec * 100 + t2.tv_usec) - (t1.tv_sec * 100 + t1.tv_usec);
elapsed_strcoll = (t3.tv_sec * 100 + t3.tv_usec) - (t2.tv_sec * 100 + t2.tv_usec);
printf("strcmp time elapsed: %d us\n",elapsed_strcmp);
printf("strcoll time elapsed: %d us\n",elapsed_strcoll);
}
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] varchar as primary key
Jeff Davis <[EMAIL PROTECTED]> writes:
> $ ./cmp
> locale set to: en_US.UTF-8
> strcmp time elapsed: 2034183 us
> strcoll time elapsed: 2019880 us
It's hardly credible that you could do either strcmp or strcoll in 2 nsec
on any run-of-the-mill hardware. What I think is happening is that the
compiler is aware that these are side-effect-free functions and is
removing the calls entirely, or at least moving them out of the loops;
these times would be credible for loops consisting only of an increment,
test, and branch.
Integer overflow in your elapsed-time calculation is probably a risk
as well --- do the reports add up to something like the actual elapsed
time?
I tried a modified form of your program (attached) on an FC6 machine
and found that at any optimization level above -O0, that compiler
optimizes the strcmp() case into oblivion, even with code added as below
to try to make it look like a real operation. The strcoll() call without
any following test, as you had, draws a warning about "statement with
no effect" which is pretty suspicious too. With the modified program
I get
$ gcc -O1 -Wall cmptest.c
$ time ./a.out
locale set to: en_US.UTF-8
strcmp time elapsed: 0 us
strcoll time elapsed: 67756363 us
real1m7.758s
user1m7.746s
sys 0m0.006s
$ gcc -O0 -Wall cmptest.c
$ time ./a.out
locale set to: en_US.UTF-8
strcmp time elapsed: 4825504 us
strcoll time elapsed: 68864890 us
real1m13.692s
user1m13.676s
sys 0m0.010s
So as best I can tell, strcoll() is pretty dang expensive on Linux too.
regards, tom lane
#include
#include
#include
#include
#include
#define ITERATIONS 1
#define THE_LOCALE "en_US.UTF-8"
int main(int argc, char *argv[]) {
int i;
char *str1 = "abcdefghijklmnop1";
char *str2 = "abcdefghijklmnop2";
char *newlocale;
struct timeval t1,t2,t3;
double elapsed_strcmp,elapsed_strcoll;
if( (newlocale = setlocale(LC_ALL,THE_LOCALE)) == NULL ) {
printf("error setting locale!\n");
exit(1);
}
else {
printf("locale set to: %s\n",newlocale);
}
gettimeofday(&t1,NULL);
for(i=0; i < ITERATIONS; i++) {
if (strcmp(str1,str2) == 0)
printf("unexpected equality\n");
}
gettimeofday(&t2,NULL);
for(i=0; i < ITERATIONS; i++) {
if (strcoll(str1,str2) == 0)
printf("unexpected equality\n");
}
gettimeofday(&t3,NULL);
elapsed_strcmp = (t2.tv_sec * 100.0 + t2.tv_usec) - (t1.tv_sec *
100.0 + t1.tv_usec);
elapsed_strcoll = (t3.tv_sec * 100.0 + t3.tv_usec) - (t2.tv_sec *
100.0 + t2.tv_usec);
printf("strcmp time elapsed: %.0f us\n",elapsed_strcmp);
printf("strcoll time elapsed: %.0f us\n",elapsed_strcoll);
return 0;
}
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[GENERAL] Casting to varchar
Just discovered (the hard way) that casting a boolean column ::varchar doesn't work. I assume I can add a function somewhere that will define a default cast for this? Are there any other standard types that can't be cast to varchar? -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(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] Casting to varchar
On 04/05/2007 21:34, Scott Ribe wrote: Just discovered (the hard way) that casting a boolean column ::varchar doesn't work. I assume I can add a function somewhere that will define a default cast for this? Are there any other standard types that can't be cast I just use something like this: create or replace function bool2str(TheValue boolean) returns varchar as $$ begin if TheValue then return 'true'; else return 'false'; end if; end; $$ language plpgsql stable; Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Casting to varchar
Scott Ribe wrote: > Just discovered (the hard way) that casting a boolean column ::varchar > doesn't work. I assume I can add a function somewhere that will define a > default cast for this? Sure, see CREATE CAST. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(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] Casting to varchar
Raymond O'Donnell wrote: > On 04/05/2007 21:34, Scott Ribe wrote: > > >Just discovered (the hard way) that casting a boolean column ::varchar > >doesn't work. I assume I can add a function somewhere that will define a > >default cast for this? Are there any other standard types that can't be > >cast > > I just use something like this: > > create or replace function bool2str(TheValue boolean) > returns varchar as > $$ > begin > if TheValue then > return 'true'; > else > return 'false'; > end if; > end; > $$ > language plpgsql stable; To complete the example, alvherre=# create cast (boolean as varchar) with function bool2str(bool); CREATE CAST alvherre=# select 't'::boolean::varchar; varchar - true (1 fila) Though I'd mark the function immutable rather than stable. alvherre=# select 'f'::boolean::varchar; varchar - false (1 fila) alvherre=# select '0'::boolean::varchar; varchar - false (1 fila) alvherre=# select '123'::boolean::varchar; ERROR: invalid input syntax for type boolean: "123" -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Casting to varchar
> Sure, see CREATE CAST. Too simple ;-) I was expecting to have to dig into data type definitions... -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: R: [GENERAL] Postgres 8.3-dev
Magnus Hagander wrote: Yeah. But look at the part about SYSTEM being the owner, I wonder if that's related. Hmm, that is odd. iirc, there is a Windows policy option that tells the installer to always run with elevated privileges. Do you know if that effectively runs installers as SYSTEM, or does it elevate the privileges of the current user somehow? Paolo; could that option be set manually, or though group policy on your system? Regards, Dave. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Casting to varchar
On May 4, 2007, at 15:34 , Scott Ribe wrote: Are there any other standard types that can't be cast to varchar? You already got an answer to the first part of your question, but I thought you might be interested in the second as well. Here's what I did: SELECT DISTINCT cast_from FROM pg_cast c NATURAL JOIN ( SELECT oid as castsource, typname as cast_from FROM pg_type ) s WHERE NOT EXISTS ( SELECT 1 FROM pg_cast i NATURAL JOIN ( SELECT oid as casttarget, typname as cast_target FROM pg_type ) t WHERE cast_target = 'text' AND i.castsource = c.castsource ) ORDER BY cast_from; cast_from -- abstime bit bool box circle lseg path polygon regclass regoper regoperator regproc regprocedure regtype reltime text varbit (17 rows) I don't know which of those you'd consider standard, but I believe that's a complete list from HEAD of a few minutes ago. Michael Glaesemann grzm seespotcode net ---(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] varchar as primary key
On Fri, May 04, 2007 at 03:42:45PM -0400, Tom Lane wrote: > It's hardly credible that you could do either strcmp or strcoll in 2 nsec > on any run-of-the-mill hardware. What I think is happening is that the > compiler is aware that these are side-effect-free functions and is > removing the calls entirely, or at least moving them out of the loops; > these times would be credible for loops consisting only of an increment, > test, and branch. It's not the compiler, it's the C library. strcmp and strcoll are defined as: extern int strcoll (__const char *__s1, __const char *__s2) __THROW __attribute_pure__ __nonnull ((1, 2)); In this context "pure" is essentially what IMMUTABLE is in postgres. Which doesn't change the fact that strcoll is expensive. Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Casting to varchar
Michael Glaesemann <[EMAIL PROTECTED]> writes: > On May 4, 2007, at 15:34 , Scott Ribe wrote: >> Are there any other standard types that can't be cast >> to varchar? > You already got an answer to the first part of your question, but I > thought you might be interested in the second as well. Note that there's a proposal to allow explicit casts to text from any type (by invoking the appropriate I/O function behind the scenes) and I imagine we'd allow casts to varchar as well. Not sure if this will happen for 8.3, although it still could. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] varchar as primary key
On Fri, 2007-05-04 at 23:45 +0200, Martijn van Oosterhout wrote: > On Fri, May 04, 2007 at 03:42:45PM -0400, Tom Lane wrote: > > It's hardly credible that you could do either strcmp or strcoll in 2 nsec > > on any run-of-the-mill hardware. What I think is happening is that the > > compiler is aware that these are side-effect-free functions and is > > removing the calls entirely, or at least moving them out of the loops; > > these times would be credible for loops consisting only of an increment, > > test, and branch. > > It's not the compiler, it's the C library. strcmp and strcoll are > defined as: > > extern int strcoll (__const char *__s1, __const char *__s2) > __THROW __attribute_pure__ __nonnull ((1, 2)); > > In this context "pure" is essentially what IMMUTABLE is in postgres. > > Which doesn't change the fact that strcoll is expensive. > Thanks for clearing that up. I should have done a sanity check on those numbers to begin with. By the way, I didn't see the warning Tom mentioned using -Wall on either system. Regards, Jeff Davis ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Update violating constraint
Alvaro Herrera wrote: > Alban Hertroys wrote: > > Richard Huxton wrote: > > > Alban Hertroys wrote: > > >> Naz Gassiep wrote: > > >>> Hi, > > >>> I'm trying to do an update on a table that has a unique constraint > > >>> on the field, I need to update the table by setting field = field+1 > > > > > >> I think you're looking for deferrable constraints; see: > > >> > > >> http://www.postgresql.org/docs/8.2/static/sql-set-constraints.html > > > > > > Which won't work with unique constraints unfortunately. That's because > > > they're implemented through a unique index. > > > > I appreciate the complexities involved, but that really ought to work on > > a single statement. I recall seeing something along these lines on the > > TODO list some time ago? > > It is still on the TODO list. If you want it to disappear from there, > your best bet is implementing a fix, followed by motivating someone to > do it for you. If you don't, bets are someone will do it eventually > (which may be too late for your taste). Yes, TODO has: o Allow DEFERRABLE and end-of-statement UNIQUE constraints? This would allow UPDATE tab SET col = col + 1 to work if col has a unique index. Currently, uniqueness checks are done while the command is being executed, rather than at the end of the statement or transaction. http://people.planetpostgresql.org/greg/index.php?/archives/2006/06/10.html http://archives.postgresql.org/pgsql-hackers/2006-09/msg01458.php -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] An alternatives to rules and triggers
Is there some way that one can determine whether a table has changed i.e. an insert, delete, update, without having to resort to setting a flag in another table using a triger or rule. I was wondering whether one of the system relations keep track of whether a table has been modifed. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] An alternatives to rules and triggers
Glen Eustace <[EMAIL PROTECTED]> writes: > I was wondering whether one of the system relations keep track of > whether a table has been modifed. Nope. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] An alternatives to rules and triggers
Glen Eustace wrote: Is there some way that one can determine whether a table has changed i.e. an insert, delete, update, without having to resort to setting a flag in another table using a triger or rule. I was wondering whether one of the system relations keep track of whether a table has been modifed. How soon do you need to know that a change has occured? I suppose one could monitor the log. You'd want to keep track of where in the log your script read up to the last time, in order to avoid having to run through from the beginning each time. And you'd want to ensure that you scan the log right before it's rotated, of course. A bit of a hack ... b ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] query not using index
Hi, Postgres is refusing to use a GIST index on a spatial column. Here's the table and column and index: Table "public.scene" Column| Type | Modifiers -+-+--- ... footprint | geometry| not null Indexes: ... "idxscenefootprint" gist (footprint) Index "public.idxscenefootprint" Column | Type ---+--- footprint | box2d gist, for table "public.scene" This table has about 8,000,000 rows. Note in the following that even when I disable sequential scans, it still does a sequential scan! db=> explain analyze SELECT * FROM scene A WHERE A.footprint && box '((-120.1, 34.3), (-119.7, 34.4))' ; QUERY PLAN --- Seq Scan on scene a (cost=0.00..369700.89 rows=42196 width=252) (actual time=50.064..47748.609 rows=507 loops=1) Filter: ((footprint)::box && '(-119.7,34.4),(-120.1,34.3)'::box) Total runtime: 47749.094 ms (3 rows) db=> set enable_seqscan = off; SET db=> explain analyze SELECT * FROM scene A WHERE A.footprint && box '((-120.1, 34.3), (-119.7, 34.4))' ; QUERY PLAN -- Seq Scan on scene a (cost=1.00..100369700.89 rows=42196 width=252) (actual time=47.405..48250.899 rows=507 loops=1) Filter: ((footprint)::box && '(-119.7,34.4),(-120.1,34.3)'::box) Total runtime: 48251.422 ms (3 rows) Also, when I look at pg_stats, there's no histogram for the footprint column (and this is right after I did an analyze): db=> select * from pg_stats where tablename='scene' and attname='footprint'; schemaname | tablename | attname | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation +---+---+---+--- ++--+--- +--+- public | scene | footprint | 0 | 109 | -1 | | | | (1 row) It's as though the index didn't even exist. I'm using PostgreSQL 8.0.3 and PostGIS 1.0.0. Thanks, -Greg ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
