[GENERAL] psql connection via localhost or 127.0.0.1
I am using postgres 9.4beta3 (Debian jessie) this is my pg_hba.conf -- localallpostgrespeer localallallmd5 hostallall127.0.0.1/32md5 hostallall::1/128md5 -- but if I use psql to connect via tcp to the server (on the same machine) I can see two differente behaviours if I use localhost or 127.0.0.1 If I understand correctly the first two lines are useful only for Unix sockets and not for tcp-ip connection. -- with localhost edoardo@host:~$ psql -h localhost -U postgres psql (9.4beta3) Connessione SSL (protocollo: TLSv1.2, cifrario: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compressione: disattivato) Digita help per avere un aiuto. postgres=# -- no password request -- with localhost edoardo@happy:~$ psql -h 127.0.0.1 -U postgres Inserisci la password per l'utente postgres: -- with password request. Why localhost is not an alias for 127.0.0.1 ? I did some search on Google but with no useful results thank you Edoardo -- 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] psql connection via localhost or 127.0.0.1
Il 06/11/14 16:00, John R Pierce ha scritto: On 11/6/2014 6:34 AM, Edoardo Panfili wrote: Why localhost is not an alias for 127.0.0.1 ? grep localhost /etc/hosts 127.0.0.1 localhost 127.0.1.1 host.host host # The following lines are desirable for IPv6 capable hosts ::1 localhost ip6-localhost ip6-loopback ff02::1 ip6-allnodes ff02::2 ip6-allrouters I've seen some weird stuff on some systems. 127.0.1.1 sounds a little bit strange form me but the file seems ok. and $ host localhost localhost has address 127.0.0.1 localhost has IPv6 address ::1 Edoardo -- 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] psql connection via localhost or 127.0.0.1
Il 06/11/14 16:08, Tom Lane ha scritto: Edoardo Panfili edoa...@aspix.it writes: I am using postgres 9.4beta3 (Debian jessie) this is my pg_hba.conf -- localallpostgrespeer localallallmd5 hostallall127.0.0.1/32md5 hostallall::1/128md5 -- but if I use psql to connect via tcp to the server (on the same machine) I can see two differente behaviours if I use localhost or 127.0.0.1 If I understand correctly the first two lines are useful only for Unix sockets and not for tcp-ip connection. Yeah. On modern Linux distros it's quite likely that localhost is resolving as IPv6 ::1, not 127.0.0.1, but in neither case would it match your local lines. In any case, your first connection attempt is definitely not connecting over a Unix socket because we never use SSL with Unix sockets. ok, thank you. I can think of two plausible theories: 1. The postmaster isn't actually using the pg_hba.conf you think it is. (Maybe you modified the file and forgot to do a pg_ctl reload?) another way on my test machine: /etc/init.d/postgresql stop /etc/init.d/postgresql start 2. You have a service file active that is capturing the server name localhost and redefining it to mean something other than the obvious meaning. http://www.postgresql.org/docs/9.3/interactive/libpq-pgservice.html A useful test would be to try psql -h ::1 and see which way that behaves. If it does the same thing as -h localhost then we can eliminate the service-file theory. connection via ::1 $ psql -h ::1 -U postgres Inserisci la password per l'utente postgres: same as for 127.0.0.1 thank you Edoardo -- 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] psql connection via localhost or 127.0.0.1
Il 06/11/14 16:18, Adrian Klaver ha scritto: On 11/06/2014 07:00 AM, John R Pierce wrote: On 11/6/2014 6:34 AM, Edoardo Panfili wrote: Why localhost is not an alias for 127.0.0.1 ? grep localhost /etc/hosts I've seen some weird stuff on some systems. I have localhost/127.0.0.1 set up correctly on my machine and I see the same behavior as the OP. I'm pretty sure it is an interaction between the libpq host/hostaddr code and a .pgpass file. I suspect the OP has a .pgpass entry for localhost but not 127.0.0.1. -- .pgpass -- localhost:5432:*:postgres:postgres_pwd removing this file localhost behaves like 127.0.0.1 maybe that the debian installer creates this file during installation (is useful but I didn't know this file). this explains the situation, thank you again to all Edoardo -- 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] psql connection via localhost or 127.0.0.1
Il 06/11/14 16:47, John R Pierce ha scritto: On 11/6/2014 7:36 AM, Edoardo Panfili wrote: grep localhost /etc/hosts 127.0.0.1 localhost 127.0.1.1 host.host host wah? whats the output of ... # ifconfig lo loLink encap:Local Loopback inet addr:127.0.0.1 Mask:255.0.0.0 inet6 addr: ::1/128 Scope:Host UP LOOPBACK RUNNING MTU:65536 Metric:1 RX packets:18367154 errors:0 dropped:0 overruns:0 frame:0 TX packets:18367154 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:0 RX bytes:23279985092 (21.6 GiB) TX bytes:23279985092 (21.6 GiB) # ifconfig lo loLink encap:Local Loopback inet addr:127.0.0.1 Mask:255.0.0.0 inet6 addr: ::1/128 Scope:Host UP LOOPBACK RUNNING MTU:65536 Metric:1 RX packets:3306 errors:0 dropped:0 overruns:0 frame:0 TX packets:3306 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:0 RX bytes:2544659 (2.4 MiB) TX bytes:2544659 (2.4 MiB) note that net mask?the loopback interface is the entire 127.0.0.0/8 networkyou can't put a host at 127.0.1.x and expect it to work right. you should instead use one of the RFC1918 reserved subnets for a private network, within 10.0.0.0/8 or 172.16.0.0/12, or 192.168.0.0/16 (you can use these with any mask size you want, for instance, /24 is usually used with 192.168.x.y) I can't figure why 127.0.1.1 is there (I will remove it) is an (almost) new installation on a virtual machine, Edoardo -- 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] psql connection via localhost or 127.0.0.1
Il 06/11/14 16:54, Adrian Klaver ha scritto: On 11/06/2014 07:47 AM, John R Pierce wrote: On 11/6/2014 7:36 AM, Edoardo Panfili wrote: grep localhost /etc/hosts 127.0.0.1 localhost 127.0.1.1 host.host host wah? https://lists.debian.org/debian-devel/2013/07/msg00809.html No dubt regarding the good reasons of the debian (and postgres) developers. All works fine, the problem is that I search the wrong thing in the documentation. Connecting to a Database section of http://www.postgresql.org/docs/9.4/static/app-psql.html contains the explanation for my situation. again, thank you Edoardo -- 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] unable to build postgres-9.4 in os x 10.9 with python
Il 30/05/14 18:49, reiner peterke ha scritto: Hi, has anyone else had this issue and does any one know the solution? Since upgrading my mac from os x 10.8 to 10.9, i can no long build postgres with '--with-python’. i get the following error. ld: framework not found Python clang: error: linker command failed with exit code 1 (use -v to see invocation) make[3]: *** [plpython2.so] Error 1 make[2]: *** [all-plpython-recurse] Error 2 make[1]: *** [all-pl-recurse] Error 2 make: *** [all-src-recurse] Error 2 my full configure command is configure '--prefix=/usr/local/postgres/9.4' '--with-perl' '--with-python' '--with-libxml' '-with-openssl' '--with-includes=/opt/local/include/' '--with-libraries=/opt/local/lib' I did I try with 9.4beta1 and --with-python using OS X 10.9.3 ./configure --with-python all goes well, have you installed the command line tools? xcode-select --install I did it to compile uuid (with no success) and postgis (with success). Edoardo
Re: [GENERAL] Is it even possible?
Il 20/03/12 15:22, Sam Loy ha scritto: I have now tried at least 7 different install methods to get pg up and running on Lion. I fear that my system is now thoroughly inoculated and will never be able to run postgres/postgis. I started with the pg mac installer / stack builder. That worked to get pg installed, but could not get postgis installed. I've now tried two different instructions using MacPort, Two using homebrew, and two using some-other-macport-homebrew-like method. NONE of them worked for me. I can see postgress running from the last install: sameloyiv 39844 0.0 0.1 2455512 6496 ?? S 9:02AM 0:00.12 /usr/local/bin/postgres -D /usr/local/var/postgres -r /usr/local/var/postgres/server.log -c unix_socket_directory=/var/pgsql_socket -c unix_socket_group=_postgres -c unix_socket_permissions=0770 root 104 0.0 0.0 2467372 1140 ?? Ss4:46PM 0:00.21 /opt/local/bin/daemondo --label=postgresql91-server --start-cmd /opt/local/etc/LaunchDaemons/org.macports.postgresql91-server/postgresql91-server.wrapper start ; --stop-cmd /opt/local/etc/LaunchDaemons/org.macports.postgresql91-server/postgresql91-server.wrapper stop ; --restart-cmd /opt/local/etc/LaunchDaemons/org.macports.postgresql91-server/postgresql91-server.wrapper restart ; --pid=none sameloyiv 40075 0.0 0.0 2434892548 s000 S+9:17AM 0:00.00 grep post sameloyiv 39849 0.0 0.0 2441352384 ?? Ss9:02AM 0:00.02 postgres: stats collector process sameloyiv 39848 0.0 0.0 2455644 1564 ?? Ss9:02AM 0:00.02 postgres: autovacuum launcher process sameloyiv 39847 0.0 0.0 2455512512 ?? Ss9:02AM 0:00.09 postgres: wal writer process sameloyiv 39846 0.0 0.0 2455512604 ?? Ss9:02AM 0:00.12 postgres: writer process But continue to see this when using psql: psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432? Hu... are you using the apple psql? Lion comes with its own copy of psql. I have many problem (access rights) with it. Try using psql that come with your postgres installation. Is there anyone who has ever successfully gotten postgres/postGIS running on Mac Lion? Really? How? I am using (compiling from surce) Postgres 8.4 and postgis 1.5 (latest versions). 8.4 because I have 8.4 on my server. Compile postgres is a no-problem operation. Compile postgis is not so easy because it needs some libraries. Is there a way to purge my system of all of the corrupted/bad installs and start over? How? I think that depends on you installation, I have all data in /var/database and simply removing that I have a clean system. How do I get pgadmin if I use homebrew/macports etc? Maybe that I didn't understand. simply download it from http://www.pgadmin.org/download/macosx.php Edoardo -- 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] PostGIS in a commercial project
Il 24/10/11 10:03, Pavel Stehule ha scritto: 2011/10/24 Thomas Kellererspam_ea...@gmx.net: Eduardo Morras, 21.10.2011 20:53: Now PostGIS is licensed under the GPL and I wonder if we can use it in a commercial (customer specific) project then. The source code will not be made open source, but of course the customer will get the source code. Is it still OK to use the GPL licensed PostGIS in this case? Is that then considered a derivative work because the application will not work without PostGIS? If it's pure GPL, then postgresql is automagically relicenced to GPL, because postgresql allows relicencing and GPL force it to be GPL. Your source code must be in GPL too. Remember, it's a virus licence and has the same problem that Midas king had. Thanks for the answer. I think we'll better be safe than sorry and we will not use PostGIS then. It doesn't mean, so you must to publish your source code on net. Your codes have to be available to your customers. That is all. You can distribute your product as service, and then you don't need to show your codes. I am developing a web system that uses postgres and postgis, my source code is released under Apache2 licence (The customers has a copy of the whole source reposotory). The server interacts using jdbc and a C function for postgres. The client (java) interacts only with my server application. I think that this is safe, I'm doing wrong? My software has to use the GPL? if I can I'd like to use Apache2 licence for my source code. Regards Edoardo -- 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] PostGIS in a commercial project
Il 24/10/11 12:19, Pavel Stehule ha scritto: 2011/10/24 Edoardo Panfiliedoa...@aspix.it: Il 24/10/11 10:03, Pavel Stehule ha scritto: 2011/10/24 Thomas Kellererspam_ea...@gmx.net: Eduardo Morras, 21.10.2011 20:53: Now PostGIS is licensed under the GPL and I wonder if we can use it in a commercial (customer specific) project then. The source code will not be made open source, but of course the customer will get the source code. Is it still OK to use the GPL licensed PostGIS in this case? Is that then considered a derivative work because the application will not work without PostGIS? If it's pure GPL, then postgresql is automagically relicenced to GPL, because postgresql allows relicencing and GPL force it to be GPL. Your source code must be in GPL too. Remember, it's a virus licence and has the same problem that Midas king had. Thanks for the answer. I think we'll better be safe than sorry and we will not use PostGIS then. It doesn't mean, so you must to publish your source code on net. Your codes have to be available to your customers. That is all. You can distribute your product as service, and then you don't need to show your codes. I am developing a web system that uses postgres and postgis, my source code is released under Apache2 licence (The customers has a copy of the whole source reposotory). The server interacts using jdbc and a C function for postgres. The client (java) interacts only with my server application. I think that this is safe, I'm doing wrong? My software has to use the GPL? if I can I'd like to use Apache2 licence for my source code. there is not clean who is customer and what is one unit. If you distribute PostGIS inside your application as one unit to customer, then your application should to use GPL. Customer: a research group that uses a server application to store data plus a client application (pure java) that exchanges data with server application (not directly with postgres). I do not distribute postgres nor postgis inside my application. The customer installs postgres, postigis and then install my application (inside tomcat) and create the db. Other people downloads a java application that exchanges data with my application. Is not so easy manage licences (even though I read it) Postgres uses licence similar to BDS PostGIS uses GPL Tomcat uses Apache2 ...and an application usually uses othe libraries. But at the end my software does not use the source code of postgis (but uses postgres include files for a C function). Regard Edoardo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] A questions on planner choices
Il 20/08/11 04:28, Tom Lane ha scritto: Edoardo Panfiliedoa...@aspix.it writes: [ poor plan for a Postgis query with ] Postgres 8.4.8 with postgis 1.5.3 I think that most of the issue here is poor selectivity estimation for the Postgis operations, particularly. I suggest that you should ask about this on the postgis mailing lists. They might well tell you to try a newer release --- they may have improved things since 1.5.3. 1.5.3 is the latest stable release, I am downloading 2.0.0SVN NOTICE: LWGEOM_gist_joinsel called with incorrect join type You should *definitely* report that to the Postgis guys, because it's a bug. I will do it Thank you Edoardo Panfili -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] A questions on planner choices
I apologize for my english and... also for the explanation perhaps not very clear. I have some doubt regarding the planner choice for my query, usually it does a very good job and I would prefer to leave free the planner but with this query I have some doubt: I use tree tables, cartellino with 2 indexes cartellino_punto_geom_4326 gist (the_geom) specimen_idspecie btree (idspecie) A view named specienomi with an index on specienomi.nome Postgres 8.4.8 with postgis 1.5.3 I can post a complete explain for each query. This is the original query SELECT specienomi.nome, cartellino.cont_nome, ST_AsGML(cartellino.the_geom) FROM cartellino, specienomi, confini_regioni WHERE confini_regioni.regione='UMBRIA' AND specienomi.nome like 'Quercus%' AND cartellino.idspecie=specienomi.id AND ST_Intersects(cartellino.the_geom,confini_regioni.the_geom4326); it tooks 4481.933 ms the planner does ((cartellino join confini_regioni) join specienomi) but I think I want to try another way. A very big enhancement with: WITH temp_que AS ( SELECT specienomi.nome AS nome, cartellino.cont_nome AS cont_nome, cartellino.id AS id, the_geom FROM cartellino, specienomi WHERE specienomi.nome like 'Quercus %' AND cartellino.idspecie=specienomi.id ) SELECT temp_que.nome, temp_que.cont_nome, ST_AsGML(temp_que.the_geom) FROM temp_que, confini_regioni WHERE confini_regioni.regione='UMBRIA' AND ST_Intersects(temp_que.the_geom,confini_regioni.the_geom4326); The time is 45.026 ms the question is: I am missing some index? or ST_Intersects behaves in a way that i don't understand? after re-reading the manual I did some other try: set from_collapse_limit=1; SELECT temp_que.nome, temp_que.cont_nome, ST_AsGML(temp_que.the_geom) FROM confini_regioni, (SELECT specienomi.nome AS nome, cartellino.cont_nome AS cont_nome, cartellino.id AS id, the_geom FROM cartellino, specienomi WHERE specienomi.nome like 'Quercus %' AND cartellino.idspecie=specienomi.id ) AS temp_que WHERE confini_regioni.regione='UMBRIA' AND ST_Intersects(temp_que.the_geom,confini_regioni.the_geom4326) ORDER BY temp_que.id; works fine 50.126 ms set join_collapse_limit=1; SELECT specienomi.nome, ST_AsGML(cartellino.the_geom) FROM confini_regioni full JOIN ( cartellino full JOIN specienomi ON (cartellino.idspecie=specienomi.id)) ON ST_Intersects(cartellino.the_geom,confini_regioni.the_geom4326) WHERE confini_regioni.regione='UMBRIA' AND specienomi.nome like 'Quercus%' is slow: 5750.499 ms and NOTICE: LWGEOM_gist_joinsel called with incorrect join type thank you Edoardo [1] Plan for the firts query --- Sort (cost=20.45..20.46 rows=1 width=931) (actual time=4457.775..4457.786 rows=76 loops=1) Sort Key: cartellino.id Sort Method: quicksort Memory: 74kB - Hash Join (cost=8.32..20.44 rows=1 width=931) (actual time=243.679..4457.658 rows=76 loops=1) Hash Cond: (cartellino.idspecie = principale.id) - Nested Loop (cost=0.00..9.81 rows=614 width=886) (actual time=4.094..4439.024 rows=18370 loops=1) Join Filter: _st_intersects(cartellino.the_geom, confini_regioni.the_geom4326) - Seq Scan on confini_regioni (cost=0.00..1.25 rows=1 width=1473036) (actual time=0.017..0.021 rows=1 loops=1) Filter: ((regione)::text = 'UMBRIA'::text) - Index Scan using cartellino_punto_geom_4326 on cartellino (cost=0.00..8.30 rows=1 width=886) (actual time=0.059..94.148 rows=32200 loops=1) Index Cond: (cartellino.the_geom confini_regioni.the_geom4326) - Hash (cost=8.28..8.28 rows=3 width=57) (actual time=0.392..0.392 rows=74 loops=1) - Index Scan using i_specie_nome_specie_like on specie principale (cost=0.01..8.28 rows=3 width=57) (actual time=0.034..0.348 rows=74 loops=1) Index Cond: ((esterna_nome(ibrido, proparte, (genere)::text, [...] (cultivar)::text) ~=~ 'Quercus'::text) AND (esterna_nome(ibrido, proparte, (genere)::text, [...] (cultivar)::text) ~~ 'Quercut'::text)) Filter: (esterna_nome(ibrido, proparte, (genere)::text, [...] (cultivar)::text) ~~ 'Quercus%'::text) Total runtime: 4481.933 ms -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] A questions on planner choices
Il 19/08/11 22:15, Scott Marlowe ha scritto: On Fri, Aug 19, 2011 at 1:05 PM, Edoardo Panfiliedoa...@aspix.it wrote: [1] Plan for the firts query --- Sort (cost=20.45..20.46 rows=1 width=931) (actual time=4457.775..4457.786 rows=76 loops=1) Sort Key: cartellino.id Sort Method: quicksort Memory: 74kB -Hash Join (cost=8.32..20.44 rows=1 width=931) (actual time=243.679..4457.658 rows=76 loops=1) Hash Cond: (cartellino.idspecie = principale.id) -Nested Loop (cost=0.00..9.81 rows=614 width=886) (actual time=4.094..4439.024 rows=18370 loops=1) The row estimate here is off by a factor of 30 or so. In this case a different join method would likely work better. It might be that cranking up stats for the columns involved will help, but if that doesn't change the estimates then we might need to look elsewhere. What's your work_mem and random_page_cost? work_mem = 1MB random_page_cost = 4 I am using an SSD but the production system uses a standard hard disk. I did a try also with set default_statistics_target=1; vacuum analyze cartellino; vacuum analyze specie; -- the base table for specienomi vacuum analyze confini_regioni; but is always 4617.023 ms Edoardo -- 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] unique across two tables
On 22/06/11 18.30, David Johnston wrote:
The only (obvious to me) way to really solve the problem - invisibly - is to
allow for table-less unique indexes that multiple tables can share and that
have a pointer to the source table for any particular entry in the index.
The other method being discussed effectively uses a physical table to
implement this behavior.
I Have the same problem: one ID must be unique.
Three tables inherits from the same parent table the id column, the ID
is is defined as:
id bigint DEFAULT nextval('sequence_name')
in the parent table.
Can I assume that a sequence ensures uniqueness?
And... I did some search on Google but can't figure table-less unique
indexes that multiple tables can share where can I find further
information regarding this thing?
thank you
Edoardo
--
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] unique across two tables
On 23/06/11 22.39, Tomas Vondra wrote:
Dne 23.6.2011 20:39, Edoardo Panfili napsal(a):
I Have the same problem: one ID must be unique.
Three tables inherits from the same parent table the id column, the ID
is is defined as:
id bigint DEFAULT nextval('sequence_name')
in the parent table.
Can I assume that a sequence ensures uniqueness?
Well, not really. A sequence may be reset (so it will generate some
values again) and some users (developers/DBAs) might use a value that
did not come from the sequence (again, a duplicity).
If you can somehow enforce that the sequence is never reset and that
it's the only source of values, then it's probably safe. But the only
way how to enforce that is to e-mail all the developers and DBAs with a
threat that everyone who does not follow this rule will be executed ...
I also think it is so, also regarding probably safe :-)
But there's a possible solution I guess - you can create a separate
table with a single column (ID) with a UNIQUE constraint. And you can
create AFTER trigger that attempts to update the table. That should
provide exactly the same protection. It's elegant, it's reliable and I
doubt you can implement a faster solution on your own.
I will try this solution in my database.
thanks to you and also to David
Edoardo
--
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] postgis 1.5.2 installation configure: WARNING: could not locate CUnit required for liblwgeom unit tests
On 07/02/11 18.55, Paul Ramsey wrote: Well, maybe you could in-place upgrade if you left your PostGIS version at the original and only upgraded the PostgreSQL part, but you aren't doing that, you're also upgrading your PostGIS version. pg_dump the database create a new database on the new server, install postgis in it pg_restore the database I am at the beginning with postigis. This is also my way to copy the data from one machine to another. I have the same server in both the machines (postgresql 8.4 + postgis 1.5), no errors during this process. ignore the many errors this sentence scares me a bit (for my future)... what kind of errors? Edoardo On 2011-02-07, at 9:49 AM, akp geek wrote: Please pardon my ignorance. The reason I am worried about it is, when I tried to use pg_upgrade to migrate from 8.3 to 9.0.2. I am getting an error Failed to load library: $libdir/liblwgeom ERROR: could not access file $libdir/liblwgeom: No such file or directory I am using pg_upgrade for upgrading Regards On Mon, Feb 7, 2011 at 12:39 PM, Paul Ramsey pram...@cleverelephant.ca mailto:pram...@cleverelephant.ca wrote: It's not a dynlib, it's statically linked at build time, so have no fear. Stop thinking so much :) P On 2011-02-07, at 9:38 AM, akp geek wrote: installation was successful. But it did not install the liblwgeom.so Regards On Mon, Feb 7, 2011 at 12:29 PM, Paul Ramsey pram...@cleverelephant.ca mailto:pram...@cleverelephant.ca wrote: It's just a warning, continue happily onwards. Just means a few unit tests won't be run. P. On 2011-02-07, at 9:27 AM, akp geek wrote: Hi All - I am trying to install postgis 1.5.2 on solaris10. When I run the configure I get the following. *configure: WARNING: could not locate CUnit required for liblwgeom unit tests* is there some setting I need to do to make it work? $./configure --prefix=/opt/postgres/gis --with-geosconfig=/opt/postgres/gis/bin/geos-config --with-projdir=/opt/postgres/gis checking build system type... sparc-sun-solaris2.10 checking host system type... sparc-sun-solaris2.10 checking for gcc... gcc checking whether the C compiler works... ^C10.112.161.124$ $./configure --prefix=/opt/postgres/gis --with-geosconfig=/opt/postgres/gis/bin/geos-config --with-projdir=/opt/postgres/gis checking build system type... sparc-sun-solaris2.10 checking host system type... sparc-sun-solaris2.10 checking for gcc... gcc checking whether the C compiler works... yes checking for C compiler default output file name... a.out checking for suffix of executables... checking whether we are cross compiling... no checking for suffix of object files... o checking whether we are using the GNU C compiler... yes checking whether gcc accepts -g... yes checking for gcc option to accept ISO C89... none needed checking for a sed that does not truncate output... /usr/5bin/sed checking for grep that handles long lines and -e... /usr/sfw/bin/ggrep checking for egrep... /usr/sfw/bin/ggrep -E checking for fgrep... /usr/sfw/bin/ggrep -F checking for ld used by gcc... /usr/ccs/bin/ld checking if the linker (/usr/ccs/bin/ld) is GNU ld... no checking for BSD- or MS-compatible name lister (nm)... /usr/ccs/bin/nm -p checking the name lister (/usr/ccs/bin/nm -p) interface... BSD nm checking whether ln -s works... yes checking the maximum length of command line arguments... 786240 checking whether the shell understands some XSI constructs... yes checking whether the shell understands +=... no checking for /usr/ccs/bin/ld option to reload object files... -r checking for objdump... no checking how to recognize dependent libraries... pass_all checking for ar... ar checking for strip... strip checking for ranlib... ranlib checking command to parse /usr/ccs/bin/nm -p output from gcc object... ok checking how to run the C preprocessor... gcc -E checking for ANSI C header files... yes checking for sys/types.h... yes checking for sys/stat.h... yes checking for stdlib.h... yes checking for string.h... yes checking for memory.h... yes checking for strings.h... yes checking for inttypes.h... yes checking for stdint.h... yes checking for unistd.h... yes checking for dlfcn.h... yes checking for objdir... .libs checking if gcc supports -fno-rtti -fno-exceptions... no checking for gcc option to produce PIC... -fPIC -DPIC checking if gcc PIC flag -fPIC -DPIC works... yes checking if gcc
Re: [GENERAL] SELECT INTO array[i] with PL/pgSQL
On 07/02/11 22.15, Julia Jacobson wrote:
Dear PostgreSQL community,
Please consider the following minimal example:
CREATE TABLE example (row_id SERIAL, value TEXT);
INSERT INTO example(value) VALUES ('val1');
INSERT INTO example(value) VALUES ('val2');
INSERT INTO example(value) VALUES ('val3');
CREATE OR REPLACE FUNCTION foo()
RETURNS TEXT
AS
$$
DECLARE
a TEXT;
b TEXT[];
i INT;
BEGIN
FOR i in 1..3 LOOP
SELECT INTO a value FROM example WHERE row_id=i; -- This works
b[i] := a; -- perfectly!
-- SELECT INTO b[i] value FROM example WHERE row_id=i; Doesn't work!
END LOOP;
RETURN b[2];
END;
$$
LANGUAGE 'plpgsql';
this one seems work...
CREATE OR REPLACE FUNCTION foo()
RETURNS TEXT
AS
$$
DECLARE
b TEXT[];
i INT;
BEGIN
FOR i in 1..3 LOOP
b[i]:= value FROM example WHERE row_id=i;
END LOOP;
RETURN b[2];
END;
$$
LANGUAGE 'plpgsql';
Edoardo
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] foreign keys and inheritance problem
hi, I am in some trouble with my tables defined using inheritance, This is a semplified test case: --- create table sub1( name1 text) inherits(father); create table sub2( name2 text) inherits(father); create table other (description text, id integer); -- I know, the contraints is not checked in sub1 and sub2 ALTER TABLE father ADD UNIQUE(id); ALTER TABLE other ADD FOREIGN KEY (id) REFERENCES father(id); insert into sub1 (id,name1) VALUES(1,'row1 in sub1'); insert into sub2 (id,name2) VALUES(2,'row1 in sub2'); select * from father; id 1 2 I can't insert data in other table: - test=# insert into other(id,description) VALUES(1,'test'); ERROR: insert or update on table other violates foreign key constraint other_id_fkey DETAIL: Key (id)=(1) is not present in table father. - Is there a way to do this thing? Or I must remove the foreign key constraint? thank you Edoardo -- 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] foreign keys and inheritance problem
On 12/08/10 18.59, Edoardo Panfili wrote: hi, I am in some trouble with my tables defined using inheritance, This is a semplified test case: --- create table sub1( name1 text) inherits(father); create table sub2( name2 text) inherits(father); create table other (description text, id integer); -- I know, the contraints is not checked in sub1 and sub2 ALTER TABLE father ADD UNIQUE(id); ALTER TABLE other ADD FOREIGN KEY (id) REFERENCES father(id); insert into sub1 (id,name1) VALUES(1,'row1 in sub1'); insert into sub2 (id,name2) VALUES(2,'row1 in sub2'); select * from father; id 1 2 I can't insert data in other table: - test=# insert into other(id,description) VALUES(1,'test'); ERROR: insert or update on table other violates foreign key constraint other_id_fkey DETAIL: Key (id)=(1) is not present in table father. - Is there a way to do this thing? Or I must remove the foreign key constraint? trigger solution, it seems ok but I am still searching for a declarative one. CREATE OR REPLACE FUNCTION insert_veto() RETURNS trigger AS $BODY$ DECLARE present boolean; BEGIN present := exists (select * from father where id=NEW.id) ; IF present THEN return NULL; ELSE RETURN NEW; END IF; END $BODY$ LANGUAGE 'plpgsql' CREATE TRIGGER veto BEFORE INSERT OR UPDATE ON other FOR EACH ROW EXECUTE PROCEDURE insert_veto(); Edoardo -- 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] foreign keys and inheritance problem
On 12/08/10 20.44, Tom Lane wrote: Edoardo Panfiliedoa...@aspix.it writes: On 12/08/10 18.59, Edoardo Panfili wrote: I am in some trouble with my tables defined using inheritance, No, foreign keys do not play very nicely with inheritance. There is some explanation in the manual, in the Caveats subsection under Inheritance --- see bottom of this page: http://www.postgresql.org/docs/8.4/static/ddl-inherit.html thank you, I must read with more attenction the page. I stop the search for a declarative solution, triggers or no check. thank you again Edoardo -- 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] Help with select with max and min please
On 08/08/10 20.47, Jose Maria Terry wrote: Hello all, I need to run a query on a table that holds logged data from several water flow meters. I need the first and last values (with their associated time) for every logger in a time range. I've tried this that returns the min and max time in the desired range for every logger, but i don't know how to get the associated data (the row called caudal) for min and max . select remota_id,min(hora),max(hora) from historicos where hora '2010-08-07' and hora '2010-08-08 00:03' group by remota_id order by remota_id; remota_id | min | max ---+-+- 01 | 2010-08-07 00:00:30 | 2010-08-08 00:00:30 02 | 2010-08-07 00:00:06 | 2010-08-08 00:00:06 03 | 2010-08-07 00:00:03 | 2010-08-08 00:00:02 04 | 2010-08-07 00:00:06 | 2010-08-08 00:00:06 05 | 2010-08-07 00:00:01 | 2010-08-08 00:00:02 06 | 2010-08-07 00:00:02 | 2010-08-08 00:00:02 07 | 2010-08-07 00:00:06 | 2010-08-08 00:00:06 09 | 2010-08-07 00:00:06 | 2010-08-08 00:00:06 (8 filas) I need some like this: remota_id | min | max | min_caudal | max_caudal ---+-+-++ 01 | 2010-08-07 00:00:30 | 2010-08-08 00:00:30 | 976.04 | 904.21 02 | 2010-08-07 00:00:06 | 2010-08-08 00:00:06 | 829.71 | 764.42 Where min_caudal is the value of caudal in hora = min() and max_caudal is the same for hora=max() this can help? select remota_id,min(hora),max(hora), (SELECT caudal FROM historicos WHERE remota_id=ooo.remota_id AND hora=min(ooo.hora)) as min_caudal, (SELECT caudal FROM historicos WHERE remota_id=ooo.remota_id AND hora=max(ooo.hora)) as max_caudal from historicos AS ooo group by remota_id order by remota_id; Edoardo The data in the table is like this: select hora,remota_id,caudal from historicos; hora | remota_id | caudal -+---+-- 2010-05-21 20:00:06 | 04 | 1201.309 2010-05-21 20:15:08 | 04 | 1201.309 2010-05-21 20:30:06 | 04 | 1219.803 2010-05-21 20:45:06 | 04 | 1225.098 2010-05-21 21:00:06 | 04 | 1238.359 2010-05-21 21:15:06 | 04 | 1241.015 2010-05-21 21:30:06 | 04 | 1241.015 2010-05-21 21:45:06 | 04 | 1246.33 2010-05-21 22:00:06 | 04 | 1248.989 2010-05-21 22:15:06 | 04 | 1235.704 2010-05-21 22:30:06 | 04 | 1222.45 2010-05-21 22:45:06 | 04 | 1201.309 2010-05-21 23:00:06 | 04 | 1203.947 2010-05-21 23:15:06 | 04 | 1219.803 2010-05-21 23:30:06 | 04 | 1275.649 2010-05-21 23:45:06 | 04 | 1280.995 2010-05-22 00:00:06 | 04 | 1294.38 2010-05-22 00:15:06 | 04 | 1299.742 2010-05-22 00:30:06 | 04 | 1294.38 2010-05-22 00:45:06 | 04 | 1294.38 2010-05-22 01:00:06 | 04 | 1299.742 Can anyone help me? Best, =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Scanned with Copfilter Version 0.84beta3a (ProxSMTP 1.6) AntiVirus: ClamAV 0.95.2/11515 - Sun Aug 8 18:16:38 2010 by Markus Madlener @ http://www.copfilter.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to import *.sql file to postgresql database
On 18/07/10 22.06, Andre Lopes wrote: Hi, I have a DUMP file with INSERT's commands. I need to import this data to postgresql database with the psql command. How can I do this task? I think psql -U user_name database_name dump_file EDoardo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] add a value to an ENUM type
I have an enum type
CREATE TYPE shapeName AS ENUM('rectangle','circle');
now I need another value: 'square'
the pg_type.oid of 'shapename is 16458
It works, but Is it safe to use this?
insert into pg_enum (enumtypid,enumlabel) VALUES('16458','square');
thank you
Edoardo
--
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] add a value to an ENUM type
On 03/09/09 22.13, APseudoUtopia wrote:
On Thu, Sep 3, 2009 at 12:52 PM, Edoardo Panfiliedoa...@aspix.it wrote:
I have an enum type
CREATE TYPE shapeName AS ENUM('rectangle','circle');
now I need another value: 'square'
the pg_type.oid of 'shapename is 16458
It works, but Is it safe to use this?
insert into pg_enum (enumtypid,enumlabel) VALUES('16458','square');
thank you
Edoardo
I have done this previously on several occasions to modify ENUM
values, so it will work. However, when I inquired about doing the
same, I was told be careful and be sure of what you're doing, so
I'll forward it on to you as well.
thank you,
I forgot to say that for me sorting order is not important on this type.
Edoardo
--
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] ORDER BY: lexicographic ordering of names
Il 18-03-2009 21:32, Bryan Herger ha scritto: All, I am porting a database from MS SQL Server to Postgres. One of the tables contains a list of names, which I would like to list alphabetically. I noticed in the “O” names the following difference: MSSQL: O’Daniel O’Neill Oliveira Oliver While PGSQL sorts as if the apostrophe was not there: O’Daniel Oliveira Oliver O’Neill I think the MSSQL output is more correct for listing names alphabetically. How can I configure or query PGSQL to get the same sort order? Some time ago I had a problem with string sorting related to my locale (but with * instead of '), now all works adding ORDER BY column USING ~~. I did I try with your data but I obtain the same sorting of MSSQL both in my locale and using ~~. Edoardo -- 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] can't figure string compare result (using also custom C function)
Sam Mason ha scritto: On Fri, Feb 13, 2009 at 10:31:49PM +0100, Edoardo Panfili wrote: SELECT idSpecie,nome FROM specienomi WHERE idspecie=37026 and nome='X Agropogon littoralis (Sm.) C.E. Hubb.'; idspecie | nome --+- 37026 | X Agropogon littoralis (Sm.) C.E. Hubb. The same query but without one condition: no results. SELECT idSpecie,nome FROM specienomi WHERE nome='X Agropogon littoralis (Sm.) C.E. Hubb.'; idspecie | nome --+-- (0 rows) I can't figure why, can someone tell me how investigate? I've just looked back in the archives and noticed that you were asking about functional indexes; you do know that if you change the definition of a function that PG doesn't know to rebuild the index don't you? That would exhibit the symptoms you're seeing; i.e. the first case is using an index on idspecie and the second is using the (out-of-date) functional index. Tank you! After you answer it is obvious (I have an IMMUTABLE at the and of function). I was working with the same function using plpgsql but it is 2.5 times slower, and plperl is slower. REINDER TABLE specie; and all works fine. Is it reported on the documentation? (this is not a critic regarding postgres very well done documentation!) I must read it with more attention. Tank you again Edoardo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] can't figure string compare result (using also custom C function)
I have this query: (1 result) SELECT idSpecie,nome FROM specienomi WHERE idspecie=37026 and nome='X Agropogon littoralis (Sm.) C.E. Hubb.'; idspecie | nome --+- 37026 | X Agropogon littoralis (Sm.) C.E. Hubb. The same query but without one condition: no results. SELECT idSpecie,nome FROM specienomi WHERE nome='X Agropogon littoralis (Sm.) C.E. Hubb.'; idspecie | nome --+-- (0 rows) I can't figure why, can someone tell me how investigate? specienomi is a view idSpecie is a numeric field (the key of another table) nome is a text field generated by a custom C function (using 18 fields (1 enumerate type, 1 boolean, 16 text). The problem arises only with particular records, when the first character of the string is generated by my function [1]. When the first character is copied from postgres parameter [2] all works fine. [1] buffer[0]='X'; buffer[1]=' '; [2] memcpy(buffer,VARDATA(part),VARSIZE(part)-VARHDRSZ) Thank you Edoardo -- 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] can't figure string compare result (using also custom C function)
Sam Mason ha scritto:
On Fri, Feb 13, 2009 at 10:31:49PM +0100, Edoardo Panfili wrote:
The problem arises only with particular records, when the first
character of the string is generated by my function [1].
When the first character is copied from postgres parameter [2] all works
fine.
[1] buffer[0]='X'; buffer[1]=' ';
[2] memcpy(buffer,VARDATA(part),VARSIZE(part)-VARHDRSZ)
I'm not much of an expert with extending PG in C; but my first
suggestion would be are you null terminating the string?
the code above is only a fragment, with a 0 at the end of the buffer
there are a lot more problems, I use SET_VARSIZE().
The problem does not affect all my records, only 6/25480.
If you are, could you include a (cut down) portion of the code that
demonstrates the problem?
the problem seems to be at the start of the string:
guidebook= SELECT idSpecie,nome FROM specienomi
WHERE nome like 'X Agropogon littoralis (Sm.) C.E. Hubb.';
idspecie | nome
--+--
(0 rows)
SELECT idSpecie,nome FROM specienomi
WHERE nome like '%X Agropogon littoralis (Sm.) C.E. Hubb.';
idspecie | nome
--+-
37026 | X Agropogon littoralis (Sm.) C.E. Hubb.
(1 row)
guidebook= SELECT idSpecie,nome FROM specienomi
WHERE nome like 'X Agropogon littoralis (Sm.) C.E. Hubb.%';
idspecie | nome
--+--
(0 rows)
The function is very ripetitive (and whith italian names for variables).
I did a try with a shorter one but can't obtain the same bug (sorry).
there is a HERE near the lines that seems to be problematic.
Edoardo
- function --
char *prefissoSottospecie=subsp. ;
#define LUNGHEZZA_PREF_SS 7
char *prefissoVarieta=var. ;
#define LUNGHEZZA_PREF_VAR 5
char *prefissoSottoVarieta=subvar. ;
#define LUNGHEZZA_PREF_SVAR 8
char *prefissoForma=f. ;
#define LUNGHEZZA_PREF_FO 3
char *prefissoRace=race ;
#define LUNGHEZZA_PREF_RACE 5
char *prefissoSublusus=sublusus ;
#define LUNGHEZZA_PREF_SUBLUSUS 9
char *prefissoCultivar=c.v. ;
#define LUNGHEZZA_PREF_CV 5
char *suffissoProParte=p.p. ;
#define LUNGHEZZA_POST_PP 5
#define TEST_IBRIDO(n)
{if(strcmp(ibrido,(n))==0){buffer[caratteriInseriti]='x';buffer[caratteriInseriti+1]='
';caratteriInseriti+=2;}}
#define INSERISCI_PARTE(parte)
{memcpy(buffer+caratteriInseriti,VARDATA(parte),VARSIZE(parte)-VARHDRSZ);caratteriInseriti+=VARSIZE(parte)-VARHDRSZ+1;buffer[caratteriInseriti-1]='
';}
// posizione degli ibridi
#define IBRIDO_GENERE genus
#define IBRIDO_SPECIE specie
#define IBRIDO_SOTTOSPECIE subspecie
#define IBRIDO_VARIETA variety
#define IBRIDO_SOTTOVARIETA subvariety
#define IBRIDO_FORMA form
#define IBRIDO_RACE race
#define IBRIDO_SUBLUSUS sublusus
#define IBRIDO_CULTIVAR cultivar
PG_FUNCTION_INFO_V1(esterna_nome);
Datum esterna_nome(PG_FUNCTION_ARGS){
char buffer[300];
int caratteriInseriti=0; // tiene il conto dei caratteri presenti in
buffer
Datum datumIbrido= PG_GETARG_DATUM(0);
bool proParte= PG_GETARG_BOOL(1);
text *genere = (PG_ARGISNULL( 2) || VARSIZE(PG_GETARG_TEXT_P(
2))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(2));
text *specieNome = (PG_ARGISNULL( 3) ||
VARSIZE(PG_GETARG_TEXT_P( 3))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(3));
text *specieAutore = (PG_ARGISNULL( 4) ||
VARSIZE(PG_GETARG_TEXT_P( 4))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(4));
text *sottospecieNome= (PG_ARGISNULL( 5) ||
VARSIZE(PG_GETARG_TEXT_P( 5))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(5));
text *sottospecieAutore = (PG_ARGISNULL( 6) ||
VARSIZE(PG_GETARG_TEXT_P( 6))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(6));
text *varietaNome= (PG_ARGISNULL( 7) ||
VARSIZE(PG_GETARG_TEXT_P( 7))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(7));
text *varietaAutore = (PG_ARGISNULL( 8) ||
VARSIZE(PG_GETARG_TEXT_P( 8))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(8));
text *sottoVarietaNome = (PG_ARGISNULL( 9) ||
VARSIZE(PG_GETARG_TEXT_P( 9))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(9));
text *sottoVarietaAutore = (PG_ARGISNULL(10) ||
VARSIZE(PG_GETARG_TEXT_P(10))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(10));
text *formaNome = (PG_ARGISNULL(11) ||
VARSIZE(PG_GETARG_TEXT_P(11))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(11));
text *formaAutore= (PG_ARGISNULL(12) ||
VARSIZE(PG_GETARG_TEXT_P(12))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(12));
text *raceNome = (PG_ARGISNULL(13) ||
VARSIZE(PG_GETARG_TEXT_P(13))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(13));
text *raceAutore = (PG_ARGISNULL(14) ||
VARSIZE(PG_GETARG_TEXT_P(14))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(14));
text *sublususNome = (PG_ARGISNULL(15) ||
VARSIZE(PG_GETARG_TEXT_P(15))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(15));
text *sublususAutore = (PG_ARGISNULL(16) ||
VARSIZE(PG_GETARG_TEXT_P(16))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(16));
text *cultivar = (PG_ARGISNULL(17) ||
VARSIZE(PG_GETARG_TEXT_P(17))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(17));
text
Re: [GENERAL] Textmatchning
A B ha scritto: Hi. I would like to compare two columns a and b and find all cases where a is a part of b, like this select * from mytable where a ilike b; but that will not give me a row in the case when a = 'foo' and b='FOOTBALL' and I want that to be a match. So how do I rewrite my expression? I can't find any way to add % to make it match the way I want. It seems that the order is important. http://www.postgresql.org/docs/8.3/interactive/functions-matching.html#FUNCTIONS-LIKE select 'foo%' ilike 'FOOTBALL'; -- f but select 'FOOTBALL' ilike 'foo%'; -- t Edoardo -- 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] immutable functions and enumerate type casts in indexes
Martijn van Oosterhout ha scritto:
On Tue, Sep 02, 2008 at 10:53:03AM +0200, Edoardo Panfili wrote:
But i have a little question about parameters of enum_out.
Datum enum_out(PG_FUNCTION_ARGS);
this is a part of my function
---
Datum esterna_nome2(PG_FUNCTION_ARGS){
int label;
label = enum_out(fcinfo);
sprintf(debug,false enum_out: \%s\ ,unrolled);
elog(LOG, debug);
---
but it works only because my enum parameter is the first (and using
fcinfo is a little obscure).
Look in the fmgr.h header for functions like DirectFunctionCall1 and
various other ways of calling functions.
Now it works! thank you to Martin and Tom.
this is a code fragment:
--
#include utils/builtins.h
PG_FUNCTION_INFO_V1(esterna_nome);
Datum esterna_nome(PG_FUNCTION_ARGS){
Datum datumIbrido = PG_GETARG_DATUM(0);
Datum labelDatum;
char *label;
labelDatum = DirectFunctionCall1(enum_out,datumIbrido);
label = (char *) DatumGetPointer(labelDatum);
if(strcmp(label,(label_constant))==0){
...
}
...
}
--
I don't know why but I need #include utils/builtins.h
The line label = (char *) DatumGetPointer(labelDatum); is essential to
use the information in strcmp() if I use directly labelDatum it does not
works (but it works inside a sprintf(buffer,%s,labelDatum)).
thank you again
Edoardo
--
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] immutable functions and enumerate type casts in indexes
Tom Lane ha scritto:
Edoardo Panfili [EMAIL PROTECTED] writes:
labelDatum = DirectFunctionCall1(enum_out,datumIbrido);
label = (char *) DatumGetPointer(labelDatum);
Just FYI, preferred style for the second line would be
label = DatumGetCString(labelDatum);
Nearly all standard data types have DatumGetFoo and FooGetDatum
macros to hide the conversion details (even if it's only a cast).
the clean version:
--
#include utils/builtins.h
PG_FUNCTION_INFO_V1(esterna_nome);
Datum esterna_nome(PG_FUNCTION_ARGS){
Datum datumIbrido = PG_GETARG_DATUM(0);
Datum labelDatum;
char *label;
labelDatum = DirectFunctionCall1(enum_out,datumIbrido);
label = DatumGetCString(labelDatum);
if(strcmp(label,(label_constant))==0){
...
}
...
}
--
thank you again!
Edoardo
--
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] immutable functions and enumerate type casts in indexes
Tom Lane ha scritto:
Edoardo Panfili [EMAIL PROTECTED] writes:
my enumerated type is (this is a subset)
CREATE TYPE hibridation AS ENUM('none','genus','specie');
function declaration
CREATE FUNCTION ename(text,boolean,text,text RETURNS text AS
'funzioniGDB.so' LANGUAGE C IMMUTABLE;
index creation (the type of ibrido is hibridation)
CREATE INDEX i_specie_nome_specie ON specie
(esterna_nome(ibrido::text,proParte,genere,specie));
the result is
ERROR: functions in index expression must be marked IMMUTABLE
Now, maybe for your purposes here it's okay to consider it immutable.
In that case what I'd suggest doing is redefining ename() to take the
enum directly. You could invoke enum_out within the function if you
really need a text equivalent.
thank you! this is the right way for me.
Now it works.
But i have a little question about parameters of enum_out.
Datum enum_out(PG_FUNCTION_ARGS);
this is a part of my function
---
Datum esterna_nome2(PG_FUNCTION_ARGS){
int label;
label = enum_out(fcinfo);
sprintf(debug,false enum_out: \%s\ ,unrolled);
elog(LOG, debug);
---
but it works only because my enum parameter is the first (and using
fcinfo is a little obscure).
I must build a FunctionCallInfo structure (I think) but how?
Edoardo
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] immutable functions and enumerate type casts in indexes
Hello,
I have a problem with enumerated types in functions parameters.
my enumerated type is (this is a subset)
CREATE TYPE hibridation AS ENUM('none','genus','specie');
function declaration
CREATE FUNCTION ename(text,boolean,text,text RETURNS text AS
'funzioniGDB.so' LANGUAGE C IMMUTABLE;
index creation (the type of ibrido is hibridation)
CREATE INDEX i_specie_nome_specie ON specie
(esterna_nome(ibrido::text,proParte,genere,specie));
the result is
ERROR: functions in index expression must be marked IMMUTABLE
Searching on google I found some explanation: the problem arises with
non immutable typea as data but I can't figure the problem (or
better, the solution) with enumerate types.
What can I do?
thank you
Edoardo
--
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] optimizer ignoring primary key and doing sequence scan
Scott Marlowe ha scritto: On Mon, Jul 14, 2008 at 1:54 PM, Chris Hoy [EMAIL PROTECTED] wrote: Hi I have a number of tables in my database where the queries appear to ignoring the primary key and doing a seq scan instead, however other tables appear to be fine. I can see any difference between them. Is their any way of determination why the otimizer isn't picking up the primary key? Version 8.3.3 windows An example of a non working table is: select * from industries where industryid = 1; Seq Scan on industries (cost=0.00..1.02 rows=1 width=116) (actual time=0.011..0.013 rows=1 loops=1) According to this there's only one row in the table. why WOULD postgresql use an index when it can just scan the one row table in a split second. I agree with you that it can depend on the size of the table but where you can read that the table contains only one row? I try with my table (39910 rows, no index on column note) explain analyze select * from table where note='single example'; Seq Scan on table (cost=0.00..2458.88 rows=13 width=327) (actual time=10.901..481.896 rows=1 loops=1) On the postgres manual I can find Estimated number of rows output by this plan node (Again, only if executed to completion.) regarding the third parameter of the explain Where is my error? Edoardo -- 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] C function and enum types parameters
Edoardo Panfili ha scritto:
I use a C function in my database from 2002, all goes well also with
postgresql 8.3 but with 8.3.1 it no longer works, the problem is with
this line:
text *hibrid = (PG_ARGISNULL( 0) ||
VARSIZE(PG_GETARG_TEXT_P( 0))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(0));
the argument number 0 is an enum defined by
CREATE TYPE hibridationLevel AS ENUM('none','genus','specie');
the error is ERROR: invalid memory alloc request size 2298488997
I can't figure by now how to correct my error, can anyone help me?
I am still working on this after further investigation I can see that
the function is not correct also under postgres 8.3 (but it not raises
the error)
Is seems OK to retrieve the enum argument using
Oid oid = PG_GETARG_OID(0);
But using this way I can see only the Oid.
Can anyone confirm that this is the correct way to work with enum inside
a function?.
This is my little test case
---function
Datum prova1(PG_FUNCTION_ARGS){
Oid oid = PG_GETARG_OID(0);
ereport(WARNING, (errmsg_internal((1) parametro1: %d, oid)));
}
---SQL definition--
CREATE FUNCTION prova1(hibridationLevel,text) RETURNS int
AS 'funzioniGDB2.so' LANGUAGE C WITH (iscachable);
---
thank you
Edoardo
--
Jabber: [EMAIL PROTECTED]
tel: 075 9142766
-
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] C function and enum types parameters
I use a C function in my database from 2002, all goes well also with
postgresql 8.3 but with 8.3.1 it no longer works, the problem is with
this line:
text *hibrid = (PG_ARGISNULL( 0) ||
VARSIZE(PG_GETARG_TEXT_P( 0))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(0));
the argument number 0 is an enum defined by
CREATE TYPE hibridationLevel AS ENUM('none','genus','specie');
the error is ERROR: invalid memory alloc request size 2298488997
I can't figure by now how to correct my error, can anyone help me?
thank you
edoardo
--
Jabber: [EMAIL PROTECTED]
tel: 075 9142766
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] group by and count(*) behaviour in 8.3
I am using this query in 8.3beta4 (compiled from source) in MacOS X 10.5.1 SELECT webName,count(*) FROM contenitore NATURAL JOIN cartellino WHERE contenitore.tipo='e' GROUP BY webName; this is the result webName | count --+--- test palermo | 36679 Herbarium Camerinensis - CAME| 36679 Herbarium Universitatis Aeserniae - IS | 36679 Herbarium Universitatis Civitatis Perusii - PERU | 36679 Herbarium Anconitanum - ANC | 36679 Test database - São Paulo| 36679 Herbarium Universitatis Genuensis - GE | 36679 Herbarium Universitatis Senensis - SIENA | 36679 Segnalazioni Siena | 36679 Herbarium Aquilanum - AQUI | 36679 (10 rows) but 36679 is the total number of row of the table. The same query in 8.1.4 retrieves the aspected result (the number of elements for each webName). Is this a bug or a change in the semantic of SQL? thank you Edoardo -- Jabber: [EMAIL PROTECTED] tel: 075 9142766 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] group by and count(*) behaviour in 8.3
Pavel Stehule ha scritto:
Hello
it works to me:
postgres=# create table c1(n varchar, e integer);
CREATE TABLE
postgres=# create table c2(n2 varchar, e integer);
CREATE TABLE
postgres=# insert into c1 values('aa',1),('bb',2),('aa',3);
INSERT 0 3
postgres=# insert into c2 values('aa',1),('bb',2),('aa',3);
INSERT 0 3
postgres=# select * from c1 natural join c2;
e | n | n2
---++
1 | aa | aa
2 | bb | bb
3 | aa | aa
(3 rows)
postgres=# select * from c1 natural join c2 where c1.e = 3;
e | n | n2
---++
3 | aa | aa
(1 row)
postgres=# select n, count(*) from c1 natural join c2 where c1.e = 3 group by n;
n | count
+---
aa | 1
(1 row)
postgres=# select n, count(*) from c1 natural join c2 group by n;
n | count
+---
bb | 1
aa | 2
(2 rows)
postgres=# select n2, count(*) from c1 natural join c2 group by n2;
n2 | count
+---
bb | 1
aa | 2
(2 rows)
can you send structure and execution plan?
Thank you for your request, the execution plan is the one from
explain (I think) but what is the structure plan?
The problema was a bug on my import in new database!
To avoid future error of this type, how can I ask to postgres wath
column is it using in natural join?
tanks again
and sorry for my error
Edoardo
Regards
Pavel Stehule
On 02/01/2008, Edoardo Panfili [EMAIL PROTECTED] wrote:
I am using this query in 8.3beta4 (compiled from source) in MacOS X 10.5.1
SELECT webName,count(*) FROM contenitore NATURAL JOIN cartellino WHERE
contenitore.tipo='e' GROUP BY webName;
this is the result
webName | count
--+---
test palermo | 36679
Herbarium Camerinensis - CAME| 36679
Herbarium Universitatis Aeserniae - IS | 36679
Herbarium Universitatis Civitatis Perusii - PERU | 36679
Herbarium Anconitanum - ANC | 36679
Test database - São Paulo| 36679
Herbarium Universitatis Genuensis - GE | 36679
Herbarium Universitatis Senensis - SIENA | 36679
Segnalazioni Siena | 36679
Herbarium Aquilanum - AQUI | 36679
(10 rows)
but 36679 is the total number of row of the table.
The same query in 8.1.4 retrieves the aspected result (the number of
elements for each webName).
Is this a bug or a change in the semantic of SQL?
thank you
Edoardo
--
Jabber: [EMAIL PROTECTED]
tel: 075 9142766
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
--
Jabber: [EMAIL PROTECTED]
tel: 075 9142766
---(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] hibernate + postgresql ?
Oleg Bartunov ha scritto: Hello, any exprerience with Hibernate + PostgreSQL ? How does this combination is flexible and smart ? Is't possible to tune selected queries ? You can evaluate also JPA, openJPA (http://openjpa.apache.org/) seems good but now I have only simple query on it. Edoardo -- Jabber: [EMAIL PROTECTED] tel: 075 9142766 ---(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] float to int
Charles.Hou ha scritto: how can i get the int value using the sql language? like this, select cost from my_money_table , the data type of cost is float. take a look at http://www.postgresql.org/docs/8.2/static/sql-expressions.html CAST ( expression AS type ) expression::type Edoardo -- Jabber: [EMAIL PROTECTED] tel: 075 9142766 ---(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] Codifica database
andant wrote: Ciao a tutti.. Devo inserire in un campo u testo, prelevato input. Il problema e che in questo testo possono essere inseriti i seeguenti caratteri: è ò à ù ì € $ £ ( ) Al momento sto usando come codifica del Database: UTF8. non me li fa inserire... Qualc'uno mi potrebbe consiliare sulla qualche altra codifica che supporti questi caratteri... Io avevo pensato a SQL_ASCII... The first pot in Italian in this mailing-list why? I am using UTF-8 and I can insert more strange characters. How are you inserting the strings into the database? Edoardo -- [EMAIL PROTECTED] AIM: edoardopn Jabber: [EMAIL PROTECTED] tel:075 9142766 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] strange (maybe) behaviour of table lock
I have a question regarding a strange behaviour (for me, maybe that this is desidered feature) of LOCK on tables. I am using postgres 8.2 I have a servlet that uses connection pools. The servlet do LOCK table,table2,table3,table4 then do some select (I am testing the code, I will put the update in the future) an then I close instruction and connection. The first 4 executions of the servlet goes well, the 5th hangs. Afther that I use psql to do the last query (the one that hangs the system) of the servlet, also psql hangs. I did a modify of the servlet LOCK table,table2,table3,table4 IN EXCLUSIVE MODE and no other modifications. I stop and restart my system, all works well. Can someone tell me if this is a desidered behaviour? (and if is possible why). If may help I can post all the queryes and table structure, they are a little long. Thank you Edoardo Panfili -- [EMAIL PROTECTED] AIM: edoardopn Jabber: [EMAIL PROTECTED] tel:075 9142766 ---(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] strange (maybe) behaviour of table lock
Tom Lane wrote: Edoardo Panfili [EMAIL PROTECTED] writes: I have a servlet that uses connection pools. The servlet do LOCK table,table2,table3,table4 then do some select (I am testing the code, I will put the update in the future) an then I close instruction and connection. The first 4 executions of the servlet goes well, the 5th hangs. Afther that I use psql to do the last query (the one that hangs the system) of the servlet, also psql hangs. Look into the pg_locks view to find out what it's blocked on. Note that LOCK by default gets an ACCESS EXCLUSIVE lock, which means it will be blocked by *any* pre-existing access, even an open transaction that merely read the table awhile back. Are you sure you really need such a strong lock? I don't need a ACCESS EXCLUSIVE thanks a lot for your help. I luk at pg_locks, when my system hangs there are (it's right) loocked tables. It seems that if I put some delay between calls to the servlet all goes well. I can change lock level but ther is something wrong. Obviously I am doiung something wrong. To unlock the tables is not sufficient close the Statement and the Connection? tanks again Edoardo -- [EMAIL PROTECTED] AIM: edoardopn Jabber: [EMAIL PROTECTED] tel:075 9142766 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] strange (maybe) behaviour of table lock
Tom Lane wrote: Edoardo Panfili [EMAIL PROTECTED] writes: It seems that if I put some delay between calls to the servlet all goes well. I can change lock level but ther is something wrong. Obviously I am doiung something wrong. To unlock the tables is not sufficient close the Statement and the Connection? Um, possibly not, if you're using connection-pooling software ... and even if you're not, I think closing the connection is asynchronous; it'd be possible to establish a new connection before the old one has terminated and released its locks. Tnaks again. Rather than closing the connection, I think you need to do something explicit to commit your transaction. I use connection.commit(); I spend many time to explain the bahaviour of the system: I some occasions the system use another connection to retrieve some information during the main connection. This explain the hangs but... why sometimes the system works. I do more tests. Thanks a lot again Edoardo -- [EMAIL PROTECTED] AIM: edoardopn Jabber: [EMAIL PROTECTED] tel:075 9142766 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] strange (maybe) behaviour of table lock
Scott Marlowe wrote: On Fri, 2006-12-08 at 09:16, Edoardo Panfili wrote: I have a question regarding a strange behaviour (for me, maybe that this is desidered feature) of LOCK on tables. I am using postgres 8.2 I have a servlet that uses connection pools. The servlet do LOCK table,table2,table3,table4 then do some select (I am testing the code, I will put the update in the future) an then I close instruction and connection. Before we go any further, what are you trying to accomplish by this lock? Perhaps there's a better postgresqlish approach than a table lock. You are right, also Tom said that. At a lower level of lock all goes well but I'd like to know what is going wrong. This is a bug of my code (obviously) and I am investigating. thak you Edoardo -- [EMAIL PROTECTED] AIM: edoardopn Jabber: [EMAIL PROTECTED] tel:075 9142766 ---(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] strange (maybe) behaviour of table lock
Tom Lane wrote: Edoardo Panfili [EMAIL PROTECTED] writes: I spend many time to explain the bahaviour of the system: I some occasions the system use another connection to retrieve some information during the main connection. This explain the hangs but... why sometimes the system works. I do more tests. Try turning on statement logging in the server. Looking at the sequence of SQL commands actually issued to the server by the different clients would probably be informative. I did some debug. I did non consider the execution of a second transaction inside the first, this is my error. The only way to avoid problem with my code is to lower the lock level, I can't avoid the nesting of the transactions. The systems works anyway two hours ago, I can't figure how to reproduce that occasion. thanks a lot to all Edoardo -- [EMAIL PROTECTED] AIM: edoardopn Jabber: [EMAIL PROTECTED] tel:075 9142766 ---(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
