Re: [GENERAL] no pg_hba.conf entry for replication connection
On 08/17/2017 05:26 AM, armand pirvu wrote:
Hi
master (172.16.26.7) and slave (172.16.26.4)
master runs on port 5433 though
SELECT pglogical.create_subscription( subscription_name := 'shw_sub',
replication_sets := '{shw_set}',
provider_dsn := 'host=172.16.26.7 port=5433 dbname=levregdb user=repuser');
ERROR: could not connect to the postgresql server in replication mode: FATAL: no pg_hba.conf
entry for replication connection from host "172.16.26.4", user "repuser", SSL
off
DETAIL: dsn was: host=172.16.26.7 port=5433 dbname=levregdb user=repuser
My pg_hba.conf from both
local replication repuser md5
host replication repuser 127.0.0.1/32 mds5
host replication repuser 0.0.0.0/0 mds5
local all repuser md5
host all repuser 127.0.0.1/32 md5
host all repuser 0.0.0.0/0 md5
So what am I missing ?
Strange is that I followed
Did you reload the configuration after changing pg_hba.conf, e.g.
"SELECT pg_reload_conf()"?
Also, looks like you have a typo:
> host replication repuser 127.0.0.1/32 mds5
> host replication repuser 0.0.0.0/0 mds5
mds5 -> md5
Regards
Ian Barwick
--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] cluster question
On 08/16/2017 02:41 PM, Alex Samad wrote: (...) > > okay think I have it setup, but when i do a switch over it gets stuck here. > > > > NOTICE: STANDBY PROMOTE successful > NOTICE: Executing pg_rewind on old master server > NOTICE: 3 files copied to /var/lib/pgsql/9.6/data > NOTICE: restarting server using '/usr/pgsql-9.6/bin/pg_ctl -w -D /var/lib/pgsql/9.6/data -m fast restart' > pg_ctl: PID file "/var/lib/pgsql/9.6/data/postmaster.pid" does not exist > Is server running? > starting server anyway > NOTICE: STANDBY FOLLOW successful From the repmgr README: >> You must ensure that following a server start using `pg_ctl`, log output >> is not send to STDERR (the default behaviour). If logging is not configured, >> we recommend setting `logging_collector=on` in `postgresql.conf` and >> providing an explicit `-l/--log` setting in `repmgr.conf`'s `pg_ctl_options` >> parameter. i.e. when the old primary is restarted with: /usr/pgsql-9.6/bin/pg_ctl -w -D /var/lib/pgsql/9.6/data -m fast restart the calling process hangs, waiting for logging output from pg_ctl. In "repmgr.conf" set "pg_ctl_options" to something like: pg_ctl_options='-l /path/to/log' Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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] Extract date from a TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL column
On 22-09-2016 12:37, Patrick B wrote: > Hi guys, > > I'm using postgres 9.2 and got the following column: > > start TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL > > > SELECT start FROM test1; > > > 2015-12-18 02:40:00 > > I need to split that date into two columns on my select: > > 2015-12-18 = date column > 02:40:00 = time column > > How can I do that without modifying any column/data? > Maybe in a select? TO_CHAR() is your friend: https://www.postgresql.org/docs/current/static/functions-formatting.html Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, RemoteDBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Streaming Replica Master-Salve Config.
On 8/5/16 12:35 AM, Periko Support wrote: > Hi. > > I'm testing streaming replica with ubuntu 14 and psql9.3. > > I'm trying to understand how this works, my plan is to setup the > slave server for pentaho reports for queries only. > > Now, my master server it has 128GB max_connections = 200 maybe I will add > more. > shared_memory=18GB. > > My slave server doesn't have that resources, is a Xeon with 32GB of > RAM with psql same version > running on ubuntu14. > > My questions, does the slave server need to have the same resources? > 128GB and the same settings in specific max_connections and > shared_memory? max_connections must be the same (or higher) on the standby as on the master: https://www.postgresql.org/docs/current/static/runtime-config-connection.html#GUC-MAX-CONNECTIONS but shared_buffers (which is what I guess you mean with "shared_memory") can be a different value appropriate to the standby's hardware resources. Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, RemoteDBA, Training & Services -- 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] upgrade to repmgr3
Hi On 08/04/2016 05:57 PM, Pekka Rinne wrote: hi! I have been using postgres 9.4 and repmgr2.0 combination and been doing replication (hot standby). Now I'd like to start doing slot based replication and have installed repmgr3 and exeuted the provided sql scripts and also added use_replication_slots=1 into repmgr.conf. The question is that what is the correct procedure to switch into using slots (max_replication_slots) in this case as the system has been set up already without them? Do I have to unregister and re-register all the standbys? Hopefully re-clone could be avoided. No reclone needed. What I tried was that I configured max_replication_hosts=5, restarted master, created some slots using select * from pg_create_physical_replication_slot(), configured one created slot into recovery.conf in the slave. What I noticed was that replication seemed to be still working after this but in repl_nodes table slot_name remained empty. Then I did standby re-register with force and slot_name was filled with repmgr_slot_2 value which is not the name I gave for the slot. I think repmgr invented this name but in the pg_replication_slots table repmgr_slot_2 does not exist. There is only the slot I created myself (active=t). So I guess this approach is not quite right. What if I just skip doing the re-register. Does is matter if slot_name remains empty in repl_nodes? This won't affect replication, however if you attempt any failover actions using repmgr (e.g. following a new master), it will probably cause problems when attempting to create a replication slot on the new master. As a workaround you can manually update the slot name in the repl_nodes table to match the one you've chosen. We'll update repmgr to better handle this kind of situation. I don't think we've had this particular use-case before, so I'll add some notes to the documentation on how best to handle it. Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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] pg_archivecleanup - Increase time files are deleted
On 06/27/2016 06:33 AM, Patrick B wrote: ...I'd like to have the wal_files stored for 24h, and then the pg_archivecleanup could do its job and delete the files.. *You cannot do that, but you can change the wal_keep_segments <https://www.postgresql.org/docs/9.4/static/runtime-config-replication.html#GUC-WAL-KEEP-SEGMENTS> on the master and reload the postgresql.conf.* *https://www.postgresql.org/docs/9.4/static/runtime-config-replication.html#GUC-WAL-KEEP-SEGMENTS <https://www.postgresql.org/docs/9.4/static/runtime-config-replication.html#GUC-WAL-KEEP-SEGMENTS>* ok.. my current wal_keep_segments is: wal_keep_segments = 256 That means there will be at least 256 files before postgres delete them? Please, in the future, remember to state your *PostgreSQL version and O/S* as options can change. Ok.. my mistake.. I'm running PostgreSQL 9.2 in a Centos 6.7 64 bits. FYI PostgreSQL 9.4 and later provide "replication slots", which will ensure that all WAL files are kept until no longer required by standbys: https://www.postgresql.org/docs/current/static/warm-standby.html#STREAMING-REPLICATION-SLOTS This removes the need for guesswork on how much WAL to retain, though you do then need to be careful that all standbys are actually consuming WAL otherwise files will be retained for ever (or until disk space runs out, whichever comes first). Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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] backup and archive postgresql data older than 6 months
On 15/08/26 19:46, Kaushal Shriyan wrote: Hi, Are there scripts which takes backup of postgresql database and archive data older than 6 months and push it to a remote server using scp or rsync method and purge/clean the local data on the hard disk at the same time meaning at any given time we have only six months of postgresql data on the postgresql database server. Have you looked at Barman ( http://www.pgbarman.org/ )? Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, RemoteDBA, Training Services -- 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] Postgresql BDR Replication Setup Issue
Hi On 15/06/09 2:31, Ravi Tammineni wrote: We are setting up 2-node Postgresql BDR replication at our place and I am getting the following error after bdr_init_copy command. (...) Could not update security label: ERROR: cannot execute DELETE in a read-only transaction (...) == Data from Node2 log file 2015-06-08 10:08:45.957 PDT LOG: entering standby mode It looks like you're trying to run BDR on a streaming replication standby. Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, RemoteDBA, Training Services -- 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] phppgadmin : login failed
On 15/05/28 17:55, arnaud gaboury wrote: (...) I can't log from the phppgadmin. I have login failed. First, I am not sure if I shall log with my unix account or postgres role (I guess the latter one). Log: LOG: database system is ready to accept connections LOG: connection received: host=[local] LOG: connection received: host=::1 port=3 FATAL: no pg_hba.conf entry for host ::1, user mediawiki, database postgres, SSL off I see there is a problem in my pg_hba.conf, but can't see how to set up it correctly. pg_hba.conf -- # TYPE DATABASEUSERADDRESS METHOD # local is for Unix domain socket connections only local all all md5 # IPv4 local connections: hostall all 127.0.0.1/32md5 # IPv6 local connections: -- Looks like you're missing an entry for IPv6 in pg_hba.conf; something like this: hostall all ::1/128 md5 Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, RemoteDBA, Training Services -- 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] Enum in foreign table: error and correct way to handle.
On 21/05/15 04:23, Peter Swartz wrote: I'm creating a foreign table (foo_table) in database_a. foo_table lives in database_b.foo_table has an enum (bar_type) as one of its columns. Because this enum is in database_b, the creation of the foreign table fails in database_a. database_a doesn't understand the column type. Running the following in database_a CREATE FOREIGN TABLE foo_table (id integer NOT NULL, bar bar_type) SERVER database_b One gets the error: ERROR: type bar_type does not exist I could just create a copy of bar_type in database_a, but this feels duplicative and possibly a future cause of inconsistency / trouble. Would anyone have thoughts on best practices for handling? A foreign table is basically an ad-hoc remote data source for the local database, so the onus is on the local database to maintain its definition of the remote table, whether it's in another (or even the same) PostgreSQL server or a completely different data source, especially as the local definition can be different from the remote one. This does mean that there's no simple way of ensuring any remote dependencies are present on the local server. PostgreSQL 9.5 will provide the IMPORT FOREIGN SCHEMA command, however this is limited to table/view definitions. Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] schema or database
On 13/04/15 11:08, Michael Cheung wrote: hi, all; I am new here. And I need some suggestion. I have many similar database to store data for every customer. Structure of database is almost the same. As I use same application to control all these data, so I can only use one database user to connect to these database. And I have no needs to query table for different customer together. I wonder which I should use, different shema or different database to store data? I 'd like to know the advantage and disadvantage for using schema or database. If as you say access to the database is via a single application database user, it will probably make more sense to use multiple schemas rather than multiple databases. Keeping everything in one database will simplify administration (e.g. making backups - ypu'll just need to dump the one database rather than looping through a variable number) and will make life easier if you ever need to do some kind of query involving multiple customers. There will also be less overhead when adding a new schema vs adding a new database. Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] unrecognized configuration parameter bdr.connections
Hi On 15/03/31 4:59, negrus wrote: Hi , all the installation of the BDR according to the documentation: https://wiki.postgresql.org/wiki/BDR_Packages I have set the following parameters: max_replication_slots = 3 max_wal_senders = 4 wal_level = 'logical' track_commit_timestamp = on shared_preload_libraries = 'bdr' max_worker_processes = 10 bdr.connections = 'master2' bdr.master2_dsn = 'dbname = master user = postgres port = 5432' with this package : postgresql-bdr94-server-9.4.1_bdr2-1_2ndQuadrant.el7.centos.x86_64 postgresql-bdr94-2ndquadrant-redhat-1.0-2.noarch postgresql-bdr94-9.4.1_bdr2-1_2ndQuadrant.el7.centos.x86_64 postgresql-bdr94-contrib-9.4.1_bdr2-1_2ndQuadrant.el7.centos.x86_64 postgresql-bdr94-libs-9.4.1_bdr2-1_2ndQuadrant.el7.centos.x86_64 postgresql-bdr94-bdr-0.9.0-1_2ndQuadrant.el7.centos.x86_64 But when try startup , not workin the BDR : 2015-03-30 15:57:53.957 PYT LOG: registering background worker bdr supervisor 2015-03-30 15:57:53.957 PYT WARNING: unrecognized configuration parameter bdr.connections 2015-03-30 15:57:53.957 PYT WARNING: unrecognized configuration parameter bdr.master2_dsn 2015-03-30 15:57:53.978 PYT LOG: redirecting log output to logging collector process As of release 0.9.0 the connection parameters are no longer defined in postgresql.conf; if present the parameters will be ignored and a warning issued, like you saw here. See the documentation for further details, particularly: http://bdr-project.org/docs/0.9/release-0.9.0.html http://bdr-project.org/docs/0.9.0/quickstart-editing.html http://bdr-project.org/docs/0.9.0/quickstart-enabling.html Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, RemoteDBA, Training Services -- 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] Pattern matching ints
On 26/01/15 20:32, Tim Smith wrote: Hi, Is there a more efficient way to pattern match integer columns other than something like : where cast(mynumber as text) ~ '.*123.*' I also seem to recall you can't create indexes on casts either ? This is perfectly possible: postgres=# CREATE TABLE foo (id INT); CREATE TABLE postgres=# CREATE INDEX idx ON foo (CAST(id AS TEXT) text_pattern_ops); CREATE INDEX postgres=# INSERT INTO foo values(generate_series(1,100)); INSERT 0 100 but not necessarily useful... postgres=# explain analyze SELECT * from foo where id::text ~ '.*12345.*'; QUERY PLAN --- Seq Scan on foo (cost=0.00..21925.00 rows=100 width=4) (actual time=17.331..961.384 rows=20 loops=1) Filter: ((id)::text ~ '.*12345.*'::text) Rows Removed by Filter: 80 Planning time: 0.296 ms Execution time: 961.411 ms (5 rows) However you might find the pg_trgm extension [1] useful: postgres=# CREATE TABLE foo (id INT); CREATE TABLE postgres=# CREATE INDEX trgm_idx ON foo using gist(cast(id as text) gist_trgm_ops); CREATE INDEX postgres=# INSERT INTO foo values(generate_series(1,100)); INSERT 0 100 postgres=# explain analyze SELECT * from foo where id::text ~ '.*12345.*'; QUERY PLAN - Bitmap Heap Scan on foo (cost=200.47..4938.11 rows=5184 width=4) (actual time=61.163..61.211 rows=20 loops=1) Recheck Cond: ((id)::text ~ '.*12345.*'::text) Heap Blocks: exact=11 - Bitmap Index Scan on trgm_idx (cost=0.00..199.17 rows=5184 width=0) (actual time=61.140..61.140 rows=20 loops=1) Index Cond: ((id)::text ~ '.*12345.*'::text) Planning time: 0.241 ms Execution time: 61.257 ms (7 rows) [1] http://www.postgresql.org/docs/current/interactive/pgtrgm.html Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] PG user group in the Kuala Lumpur area?
On 15/01/18 23:12, Torsten Förtsch wrote: Hi, I was looking for a PG user group around KL. I know there is one in Singapore. As it happens, Chris Travers, the PG contact for Malaysia is a friend of mine. So, I asked him. He wasn't aware of one either. However, he very much appreciated the idea of founding one. I know there are lots of PG users in the area. But is there enough demand for a user group? If you are interested, please contact me. My idea behind this whole thing is to eventually have a regular PG conference South East Asia. I have been to PGconf.eu several times and I know from experience that it is a great opportunity to learn new stuff, meet people and also have much fun. I think esp. Malaysia is a good place for such an event. There are many people out there that could never come to PGconf.eu or similar in the US and in many other places because of their passport. Getting a visa to Malaysia is possible for almost everyone. I don't know about North Korea, but there are many Iranians around here. About myself, I am German, currently traveling back and forth between Germany and Malaysia. I've yet to meet someone from Malaysia, but there's a SE Asia PostgreSQL group on Facebook which I seem to have been added to recently: https://www.facebook.com/groups/PGSQL.sg/?fref=ts The last Japan PG conference had an international track which was mainly Asian, and there's talk of setting up a PGasia conference. (Personally I'm British but kind of from Germany, now in Japan). Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, RemoteDBA, Training Services -- 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 issue
On 14/10/08 12:51, Stephen Davies wrote: I am in the process of migrating a bunch of databases and associated CGI scripts from 9.1.4 to 9.3 (and from 32-bit to 64-bit). The database migration has been successful but I have an issue with psql connections from CGI scripts. I can connect to the 9.3 server locally with psql from the command line, with psql from other boxes on the LAN via TCP, via JDBC from programs and servlets but cannot connect locally via CGI. If I run any of the CGI scripts from the command line they work but when invoked by Apache, they fail with the usual question as to whether anything is listening on socket /tmp/.s.PGSQL.5432. Running netstat -an shows: tcp0 0 0.0.0.0:54320.0.0.0:* LISTEN tcp6 0 0 :::5432 :::*LISTEN unix 2 [ ACC ] STREAM LISTENING 29773945 /tmp/.s.PGSQL.5432 unix 3 [ ] STREAM CONNECTED 30139402 /tmp/.s.PGSQL.5432 which I believe confirms that the socket is there and is used by local command line psql connections. Why would CGI connections fail? It's possible that whatever driver the CGI scripts use is expecting to find the socket in another directory, e.g. /var/run/postgresql/. Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] installing on mac air development machine
On 14/10/03 7:50, john.tiger wrote: we've always installed on linux so need help with a new mac air running latest osx in the instructions it shows several methods: 1) enterprisedb (but this does not look open source ?) 2) fink 3) macports 4) source etc what do most use ? thks There are three main package management systems for OS X - Fink, MacPorts and Homebrew. Most people swear by one and swear at the others. If you want a more Linux-like package management experience, one of these will be the way to go; if you just need PostgreSQL up and running, one of the binary application installers may be easier to manage. There's a useful overview of options here in case you haven't already seen it: http://www.postgresql.org/download/macosx/ Personally I build from source for development work and Macports for general package management. ps: is there a mac build for 9.4 beta 3 yet ? we would use that if available and not too hard to install beta3 has not yet been released (October 9th is the scheduled date). Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] UPDATE table: Syntax to Remove Terminal '\n'
On 14/08/28 7:31, Rich Shepard wrote: I have some rows in a table where a column attribute has a newline (\n) appended to the string. How do I represent that newline character in a SQL statement using psql? I've tried adding E'\n' to the end of the string but that doesn't work. Here's what I see when I select distinct for that column: StarvationCrk+ That's a blank line below the name. TIA, Not sure what you mean by doesn't work; if you want a more precise rendering of the newline character, the unicode linestyle (suggested by Tom Lane in the previous thread) should do the trick: postgres= \pset linestyle unicode Line style (linestyle) is unicode. postgres= SELECT E'foo\n'; ?column? ── foo ↵ (1 row) Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] UPDATE table: Syntax to Remove Terminal '\n'
On 14/08/28 8:04, Ian Barwick wrote: On 14/08/28 7:31, Rich Shepard wrote: I have some rows in a table where a column attribute has a newline (\n) appended to the string. How do I represent that newline character in a SQL statement using psql? I've tried adding E'\n' to the end of the string but that doesn't work. Here's what I see when I select distinct for that column: StarvationCrk+ That's a blank line below the name. TIA, Not sure what you mean by doesn't work; if you want a more precise rendering of the newline character, the unicode linestyle (suggested by Tom Lane in the previous thread) should do the trick: postgres= \pset linestyle unicode Line style (linestyle) is unicode. postgres= SELECT E'foo\n'; ?column? ── foo ↵ (1 row) And to remove the newline character you can do something like this: postgres= SELECT regexp_replace(E'foo\n', E'\n$',''); regexp_replace foo (1 row) Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] SELECT 'NOW()' - OK, SELECT 'CLOCK_TIMESTAMP()' - ERROR
On 14/08/22 15:40, Piotr Gasidło wrote: Hello, I found strange PostgreSQL 9.3 behavior: select now()::timestamp, 'now()'::timestamp; now | timestamp + 2014-08-22 08:34:00.883268 | 2014-08-22 08:34:00.883268 Second column is now() in single apostrophes. Now, I tried similar function, clock_timestamp() and get: select clock_timestamp()::timestamp, 'clock_timestamp()'::timestamp; ERROR: invalid input syntax for type timestamp: clock_timestamp() LINE 1: select clock_timestamp()::timestamp, 'clock_timestamp()'::ti... ^ Why is NOW() so special? Where is it documented? Here: http://www.postgresql.org/docs/current/interactive/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT All the date/time data types also accept the special literal value 'now' to specify the current date and time and also here: http://www.postgresql.org/docs/9.3/static/datatype-datetime.html#AEN5861 Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Appended '+' in Column Value
On 14/08/23 6:46, Rich Shepard wrote: One column in a table has values for the attribute 'stream'. Some queries return some rows where a stream name (only identified one so far) has an appended '+'. I cannot update the table to remove that appended character, and I've not seen this before. Example: 2220 | STV | 2012-07-12 | Nematoda | | | | | Omnivore |50 | | StarvationCrk+| Owyhee | || | | | | | | | | | 2701 | STV-10 | 2013-07-10 | Nematoda | | | | | Omnivore |36 | | StarvationCrk | Owyhee I'd appreciate learning where that '+' originates and how to get rid of it. A query to count the rows with the appendage returns zero: select count(*) from benthos where stream = 'StarvationCrk'; count --- 204 select count(*) from benthos where stream = 'StarvationCrk+'; count --- 0 You have a newline character. Try: select count(*) from benthos where stream = E'StarvationCrk\n'; Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Global value/global variable?
On 19/06/14 11:50, Edson Richter wrote:
It is possible to define a global value/variable in PostgreSQL in a way that I
can use it in any query/view/function?
For example, I do have a connection string I use for dblink connections in
several places (specially, inside views).
Then, if I want to change the connection string, I do have to change every view
manually.
If I can set a kind of global variable, then I just use it inside every view - then, at
my application startup (or even at PostgreSQL startup, if I can set this string at
postgresql.conf level), would set this global variable to point the current
string.
Your enlightment will be really welcome.
There's no such thing as a global variable, but why not use a table
to store any global configuration values? You can always do something like this:
SELECT dblink_connect('myconn', (select connstr from dblink_conf))
Regards
Ian Barwick
--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training Services
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres 9.2.4 - rebuild PostgreSQL using --with-libxml
On 14/06/12 17:23, Khangelani Gama wrote: Hi all (...) I then did the following: 1. We installed library 'xml2' (version = 2.6.23) 2. ./configure --with-libxml 3. gmake 4. gmake install I did not touch the database. They‘re getting the same error. Please advise if there was something else I needed to do. Was the database server restarted after the new version was built? Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres 9.2.4 - rebuild PostgreSQL using --with-libxml
On 14/06/12 18:06, Khangelani Gama wrote: On 14/06/12 17:23, Khangelani Gama wrote: Hi all (...) I then did the following: 1. We installed library 'xml2' (version = 2.6.23) 2. ./configure --with-libxml 3. gmake 4. gmake install I did not touch the database. They‘re getting the same error. Please advise if there was something else I needed to do. Was the database server restarted after the new version was built? It was not restarted - Should I do so? Yes, otherwise the old binary will still be running. Do also take note of Craig Ringer's email on this thread; in particular you should always run the latest minor version, which is currently 9.2.8. Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Fwd: [GENERAL] How to ignore blank lines with file_fdw
On 22/04/14 21:09, Nicklas Avén wrote: Hallo I am struggling to find the best solution to ignore blank lines in csv-file when using file_fdw. A blank line makes the table unreadable. I would like to avoid manipulating the file directly and avoid the need to make a new corrected copy of the file. I am on Linux so I have found a solution when using COPY: COPY test_table from program 'sed ''/^ *$/d'' /opt/builds/inotify_test/test.csv' with (format 'csv', header 'true'); but since the program option not seems to be implemented in file_fdw I am still searching for a solution. file_fdw uses the same mechanism internally as COPY table FROM '/file.csv'; I don't think there's currently a way for this mechanism to ignore blank lines. Unfortunately CSV is not exactly a well-defined standard, so it's debatable whether it's worth modifying the mechanism to cope with this situation. The closest thing to a standard, RFC 4180 ( http://tools.ietf.org/html/rfc4180 ) doesn't seem to have anything to say about them; on the other hand LibreOffice Calc will happily import files with blank lines. I have also found in an email from 2011 http://www.postgresql.org/message-id/4e699de6.8010...@gmail.com that when force_not_null was implemented in file_fdw the patch also included some cosmetic changes such as removing useless blank lines. But I do not find that blank lines is removed in general since I cannot read csv-files with blank lines, and I do not understand how the option force_not_null can do the trick since that is on the column level and not lines/row. The blank lines referred to here are in the source code itself. Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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 note on pg_upgrade and missing pg_upgrade_support.so
2010/9/21 Bruce Momjian br...@momjian.us: Bruce Momjian wrote: Ian Barwick wrote: Hi Just a quick note for anyone else building 9.0 from source and experimenting with pg_upgrade - if you get a message like the following when running the pg_upgrade binary: pg_upgrade_support.so must be created and installed in /path/to/pg90/lib/postgresql/pg_upgrade_support.so you need to build the the separate pg_upgrade_support as well. Well, that is step #4: http://www.postgresql.org/docs/9.0/static/pgupgrade.html 4. Install pg_upgrade Install pg_upgrade and pg_upgrade_support in the new PostgreSQL cluster Was that not clear enough? I hope my comment didn't sound insulting. I really want to know how that doc item can be made clearer. No insult taken :) .With the benefit of hindsight it's plenty clear; the problem was: a) I was doing this in a hurry (had a small amount of time to kill and a dev machine with an older beta on it) b) got sidetracked by this thread which appears to describe the same problem: http://archives.postgresql.org/pgsql-testers/2010-06/msg0.php and which was popping up pretty high in Google. Looking over the doc page again, if scanning over it, it's a bit easy to misread it as something like Install pg_upgrade for pg_upgrade support in the new PostgreSQL cluster. To my shame I hadn't even looked at pg_upgrade before, so was coming to it from a point of zero knowledge. How about a slightly more pedantic phrasing such as: -- 4. Install pg_upgrade pg_upgrade requires the installation of the contrib modules pg_upgrade and pg_upgrade_support in the new PostgreSQL cluster -- (maybe pg_upgrade and pg_upgrade_support could be highlighted in some way, e.g. with the courier-style font if that fits in with the doc functions). Anyway, it worked fine and I have since used it to upgrade to 9.0 on my personal production server with minimal downtime. Thanks for yet another great release Ian Barwick -- 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 note on pg_upgrade and missing pg_upgrade_support.so
Hi Just a quick note for anyone else building 9.0 from source and experimenting with pg_upgrade - if you get a message like the following when running the pg_upgrade binary: pg_upgrade_support.so must be created and installed in /path/to/pg90/lib/postgresql/pg_upgrade_support.so you need to build the the separate pg_upgrade_support as well. Also, the current first hit on Google for pg_upgrade is this page: http://www.postgresql.org/files/documentation/books/aw_pgsql/node267.html which is hopelessly outdated (the pg_upgrade utility described appears to have been obsoleted 5 years or so ago, see: http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/contrib/pg_upgrade/Attic/pg_upgrade ) HTH Ian Barwick -- 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] MySQL versus Postgres
2010/8/6 Rikard Bosnjakovic rikard.bosnjako...@gmail.com: On Fri, Aug 6, 2010 at 04:41, John Gage jsmg...@numericable.fr wrote: [...] So, perhaps what is needed in any sort of battle with MySQL is an introductory documentation that gives specific examples of how to achieve oh wow! worthwhile results quickly with Postgres. Bruce Momjian's book is an excellent primer: http://www.postgresql.org/docs/books/awbook.html It is *very* outdated (I remember using that to get started myself, almost 10 years ago) and a lot has been improved since then. Ian Barwick -- 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] sql dump
2010/7/27 Scott Frankel fran...@circlesfx.com: Hi all, Is it possible to perform an SQL Dump without using pg_dump? I have a special case situation wherein my application has access to a remotely-hosted PG (8.3) database, but does not have access to its admin tools. (There's a longer backstory here that I'm happy to explain if necessary.) I'm looking for an efficient way to dump all the data in the DB without having to SELECT * on each table. COPY might be of use: http://www.postgresql.org/docs/8.3/interactive/sql-copy.html though you will have to specify each table of course. Ian Barwick -- 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] [offtopic] How do you name a table...
2010/4/8 Ognjen Blagojevic ogn...@etf.bg.ac.rs: Hi, How do you name a table which sole purpose is to store a list of values? (...) Is this: a. Lookup table b. Classifier c. Cypher(er)? I'm looking for the appropriate term in English. I'd call it a lookup-table. Ian Barwick -- 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] Unexpected result from selecting an aliased but non-existing column called name
Hi Adrian, 2010/3/9 Adrian Klaver adrian.kla...@gmail.com: On Tuesday 09 March 2010 12:50:45 am Ian Barwick wrote: Hi I was wondering where some spurious information in a query was coming from - it looked like something was inserting all the values of a table row as a comma-separated list. It turns out I was attempting to reference a non-existent column called (literally) name, which instead of throwing an error produces the aforementioned list. This only seems to happen with name, and only if it is referenced with the table name or alias. Ian Barwick See this recent thread for explanation: http://archives.postgresql.org/pgsql-general/2010-02/msg01038.php Thanks for the info, apologies for the delay in replying. Certainly explains what's going on, though it seems a bit of a gotcha. Regards Ian Barwick -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Unexpected result from selecting an aliased but non-existing column called name
Hi I was wondering where some spurious information in a query was coming from - it looked like something was inserting all the values of a table row as a comma-separated list. It turns out I was attempting to reference a non-existent column called (literally) name, which instead of throwing an error produces the aforementioned list. This only seems to happen with name, and only if it is referenced with the table name or alias. To reproduce: test= CREATE table xx(id int, val text); CREATE TABLE test= INSERT INTO xx values(1,'hello world'); INSERT 0 1 test= SELECT name FROM xx; ERROR: column name does not exist LINE 1: SELECT name FROM xx; test= SELECT xx.name from xx; name --- (1,hello world) (1 row) test= SELECT xx.foobar FROM xx; ERROR: column xx.foobar does not exist LINE 1: SELECT xx.foobar FROM xx; Reproducible on 8.4.1 and 8.4.2; does not work in 8.3.1 (old test version I happen to have hanging around). Questions: - is this a feature? - and if so, where is it documented? (given that the key word in this is name, this is a tricky one to research). Thanks for any pointers. Ian Barwick -- 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] PostgreSQL Conference 2009 Japan
2009/9/2 Markus Wanner mar...@bluegap.ch: Hi, I've added a wiki page with some information you might find helpful, if you are attending the PostgreSQL Conference 2009 in Japan. However, I've never been to Tokyo before, so please feel free to correct and add better links, hints and recommendations: http://wiki.postgresql.org/wiki/PostgreSQL_Conference_2009_Japan I'd personally like to stay in a hotel with other fellow hackers, as those late night discussions tend to be very inspiring as well. So, what hotel do you plan to stay at? I'm actually living / working in Tokyo and though I'm not sure whether I'll be able to attend all of the conference I will certainly be around and it would be great to meet up. Also, if anyone needs any help / advice / translation etc., feel free to ask and I will do my best. Ian Barwick -- 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] Converting Rows to Columns
2009/5/5 Joshua Berry yob...@gmail.com: Greetings all, I know that this is an elementary question, so I'm just asking for a pointer in the right direction. I have a query like this that tries to link Retail shops (from the table aliased as 'a') to sales reps (from the table aliased as 'e') that service the given Retail shop: select a.id_pdv, e.apellido ||', ' || e.nombre as seller_name from tbl_pdvs a join tbl_circuitos b using (id_circuito) join tbl_frecuencias c on b.id_circuito = c.id_circuito join tbl_rutas d on c.id_ruta = d.id_ruta join tbl_vendedores e on d.vendedor = e.id_vendedor order by a.id_pdv limit 4; id_pdv | seller_name -+--- 101 | COLON, CRISTOBOL 101 | LOPEZ, CARLOS 102 | COLON, CRISTOBOL 102 | LUGO, FERNANDO As you can see, there are two salesmen that service this shop. What I need is to be able to create a report that has each unique id_pdv as a row of the result set, with three columns available to show up to three salemen that are assigned to the route. So this would give something like this: id_pdv | seller_name1 | seller_name2 | seller_name3 -+--++ 101 | COLON, CRISTOBOL | LOPEZ, CARLOS | (\N or '') 102 | COLON, CRISTOBOL | LUGO, FERNANDO | (\N or '') Note that the order of the sellers does not matter. Any tips? I've googled 'sql convert rows to columns' and got some results that appear to be mssql specific. On Stackoverflow there is an example of how to convert columns to rows using UNION, but not visa versa. Quick and dirty solution off the top of my head for the problem described: CREATE TABLE salesperson ( id SERIAL PRIMARY KEY, name TEXT ); CREATE TABLE salesperson_store ( salesperson_id INT NOT NULL, store_id INT NOT NULL, PRIMARY KEY (salesperson_id, store_id) ); INSERT INTO salesperson VALUES (1, 'COLON, CRISTOBOL'), (2, 'OPEZ, CARLOS'), (3, 'LUGO, FERNANDO'); INSERT INTO salesperson_store VALUES (1, 101), (1, 102), (2, 101), (3, 102); SELECT store_id, (SELECT sp.name FROM salesperson sp INNER JOIN salesperson_store sps ON sp.id=sps.salesperson_id AND sps.store_id=store.store_id ORDER BY sp.name LIMIT 1 OFFSET 0) AS seller_name1, (SELECT sp.name FROM salesperson sp INNER JOIN salesperson_store sps ON sp.id=sps.salesperson_id AND sps.store_id=store.store_id ORDER BY sp.name LIMIT 1 OFFSET 1) AS seller_name2, (SELECT sp.name FROM salesperson sp INNER JOIN salesperson_store sps ON sp.id=sps.salesperson_id AND sps.store_id=store.store_id ORDER BY sp.name LIMIT 1 OFFSET 2) AS seller_name3 FROM (SELECT DISTINCT(store_id) FROM salesperson_store) store ORDER BY store_id; store_id | seller_name1 | seller_name2 | seller_name3 --+--++-- 101 | COLON, CRISTOBOL | OPEZ, CARLOS | 102 | COLON, CRISTOBOL | LUGO, FERNANDO | (2 rows) (No doubt there are probably more elegant ways of doing this) HTH Ian Barwick -- 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] Noob Q: Is the PG database its own source code backup?
2009/4/28 Kenneth Tilton kentil...@gmail.com: I find myself hacking away in pgAdmin most of the time now, after early on keeping PG source code in text files I could preserve in SVN. At this point I cannot point to anything other than the pg db itself that has a full description. Is this normal? Or do folks assiduously maintain an external set of scripts and always work from those, resisting the temptation to just hack at the db with pgAdmin or psql? Or___? By source code you mean the definitions of database objects (tables, views, functions etc.?) Usually with the projects I work with, I treat these as an integral part of the application code and is maintained as a matter of course in the source repository. I usually maintain a master file (or set of files) containing the object definitions, a file (or script) to create an initial application database setup from these; and a file which contains the SQL required to make the changes between application releases. Ian Barwick -- 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] Smartest way to resize a column?
2009/1/12 Phoenix Kiula phoenix.ki...@gmail.com: I am trying to resize a column on a large-ish database (with 5 million rows). The column was 20 characters before, now I want to make it 35 characters. Challenge is: this is the main indexed column in a busy database. I tried looking at the ALTER TABLE commands available and there seems nothing that allows me to simply change column size from varchar(20) to varchar(35)? The syntax you want (at least in more recent PostgreSQL versions, you don't mention which version you have) is: ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(35) HTH Ian Barwick -- 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] Howto return values from a function
2008/5/16 A B [EMAIL PROTECTED]: How you generate the results is up to you. when you have them you either use RETURN NEXT or RETURN QUERY to return them to the caller. Now I get the reply ERROR: set-valued function called in context that cannot accept a set CONTEXT: PL/pgSQL function actionlist line 11 at return next You probably need to do SELECT * FROM actionlist(123) Ian Barwick -- 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] tsearch2 best practices
2007/11/18, Mag Gam [EMAIL PROTECTED]: Hi All, Planning to implement tsearch2 for my websitem and dbschema. I wanted to know if there is a Best practices guide I should be following. While reading about it, I noticed there were lot of 'gotchas' with this, such as back-up/restore, Slony 1 replication issues, etc.. What do most people recommend for backup/restore solution: 1) Install tsearch 2)Alter tables for tsearch2, update trigger, 3) do work .. 4) uninstall tsearch2, 5)backup? Is that the right approach? Or something else? Also, when will tsearch2 part of core, instead of contrib? tsearch2 has been integrated into the upcoming 8.3 release (currently beta). -- http://sql-info.de/index.html ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] IP addresses
2007/11/19, Tom Allison [EMAIL PROTECTED]: I am planning on doing a LOT of work with ip addresses and thought that the inet data type would be a great place to start. But I'm not sure how this works in with accessing the addresses. In perl or ruby how is the value returned? In Perl the value is returned as a scalar. Or should I stricly use host() and other functions to be explicit about what I'm doing. Another question. Given a subnet (eg: 192.168.1.0/24) is there some way to pull all the addresses therein? I can do this in code - but I was curious if there was a postgres way of doing it (didn't see any, but..) You want the network address functions and operators, I presume: http://www.postgresql.org/docs/8.2/interactive/functions-net.html HTH Ian Barwick -- http://sql-info.de/index.html ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] stripping HTML, SQL injections ...
Martin, 2000/11/15, Martin Gainty [EMAIL PROTECTED]: Scott- In JavaScript http://www.java2s.com/Tutorial/JavaScript/0520__Regular-Expressions/StripHTM L.htm I don't remember what the consensus was back in 2000 (your mail's timestamp), but in 2007 it's Not A Good Idea to rely on client-side validation for security-related operations ;). Regards Ian Barwick -- http://sql-info.de/index.html ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Upper and Lower-cased Database names?
2007/10/10, Daniel B. Thurman [EMAIL PROTECTED]: I am finding out for the first time that by having a database created with the name: MyTest, I cannot do a simple query as follows: postgres=# select * from MyTest.public.cars; ERROR: cross-database references are not implemented: mytest.public.cars Notice, however since I created a cars table in the postgres database, I was able to do a query: postgres=# select * from postgres.public.cars ; carid | name | vendor | type ---+--++-- H1| Civic| Honda | FF N1| Skyline GT-R | Nissan | 4WD T1| Supra| Toyota | FR T2| MR-2 | Toyota | FF (4 rows) So the problem, it seems that mixed case database names might not be supported with pssql? I have a feeling that the default character set is SQL-ASCII and should be changed to something else? What might that be and how can I change/update the character-set (encoding)? PostgreSQL doesn't support cross-database references, as per the error message, i.e. you can only perform queries on the current database. *However*, the syntax works when the named database is the same as one you're connected to. If you do \c MyTest mytest=# select * from MyTest.public.cars; the query will work (case is not the problem here). HTH Ian Barwick -- http://sql-info.de/index.html ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Why is default value not working on insert?
2006/8/8, Chris Hoover [EMAIL PROTECTED]: (...) The reason for the null being passed to to_date is this is on of almot 90k lines of data we are trying to load, and the script was built to generate this code. Since to_date(null,'mmdd') returns a null, why is the default not working? Because you're trying to explicitly insert a NULL into a column which is specified as NOT NULL. (If you can't change the script to output DEFAULT or the explicit default value, the best workaraound would be to create a trigger which converts any attempt to insert a NULL value into that column to the intended default value). Ian Barwick ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL Server Crash using plPHP or PL/Perl
2006/7/11, Carl M. Nasal II [EMAIL PROTECTED]: (...) Any ideas of what is causing the server to crash will be helpful. Below are the lines from the PostgreSQL serverlog file when the crash occurs: LOG: server process (PID 29153) exited with exit code 255 LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process (...) It would be helpful if you provided the PostgreSQL version you're using as well as details of the OS and possibly the hardware. I've seen this kind of error in connection with hardware errors (typically bad RAM or severe hard disk errors). Have you attempted replicating this problem on another system to confirm / exclude this as a possible cause? Ian Barwick -- http://sql-info.de/index.html ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] MediaWiki and Postgresql?
On 4/6/06, Jim Nasby [EMAIL PROTECTED] wrote: (...) If I'm not confusing wiki's (and I'm offline now, so I can't check), Mediawiki is pretty un-interested in supporting PostgreSQL (hard to imagine why, given that some of their folks are paid by MySQL AB), so a fork was created on pgFoundry. I believe it's called pgpedia. That'd be wikipgedia: http://pgfoundry.org/projects/wikipedia/ Ian Barwick ---(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] MediaWiki and Postgresql?
On 4/4/06, Scott Marlowe [EMAIL PROTECTED] wrote: (...) Given that this page: http://www.mediawiki.org/wiki/Help:%24wgDBtype says: Use mysql for working code and PostgreSQL for development/broken code. The documentation in the MediaWiki wiki isn't always up to date, I suspect. I'd guess that PostgreSQL support is not quite at the same level as MySQL just yet. :) FWIW there's a file includes/DatabasePostgreSQL.php in the CVS head versions of MediaWiki which has at the top: /** * This is PostgreSQL database abstraction layer. * * As it includes more generic version for DB functions, * than MySQL ones, some of them should be moved to parent * Database class. * * STATUS: Working PG implementation of MediaWiki * TODO: Installer support * * @package MediaWiki */ (There's also includes/DatabaseOracle.php but it doesn't say anything about status). Ian Barwick ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL Gotchas
On 10/7/05, Michael Fuhr [EMAIL PROTECTED] wrote: On Thu, Oct 06, 2005 at 05:47:36PM -0500, Jeffrey Melloy wrote: The only thing I could see actually being an issue is the random() one and add missing from. The rest are trivial. The random() thing is interesting, esoteric, and probably has never been a problem in a real situation. (Or has exactly once, when he wrote that gotcha) The random() issue has a workaround that the gotchas page doesn't mention: SELECT id, is_true FROM (SELECT id, RANDOM() 0.5 AS is_true FROM some_table OFFSET 0) AS t_tmp WHERE is_true; Tom Lane brought up the OFFSET 0 trick a couple of days ago in the Avoiding evaluating functions twice thread: http://archives.postgresql.org/pgsql-general/2005-10/msg00107.php Thanks, I've updated the list. I'd like to take the opportunity to point out that following the original, unexpected success of the MySQL gotchas list, I created one for PostgreSQL for the sake of balance. Though I'm really having to scrape the barrel for material ;-). The MySQL list is a little outdated; I'm going through it with updates for version 5. Ian Barwick ---(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] PostgreSQL 8.1 vs. MySQL 5.0?
On 10/7/05, Philip Hallstrom [EMAIL PROTECTED] wrote: But no, Mark, I'm not worried by the FUD. It just means there's nothing real for them to throw at PostgreSQL. This just appeared on slashdot... MySQL To Be Ikea Of The Database Market http://developers.slashdot.org/article.pl?sid=05/10/07/1224213from=rss From the linked article... http://www.cbronline.com/article_news.asp?guid=9231B8BD-3788-4DB2-B85F-707E75857B58 While new entrants into the open source database market, such as EnterpriseDB and Pervasive Software, have made no secret of their intentions to chase Oracle's market share, Mr Mickos said MySQL is happy to leave them to it. We are thankful that they are there to define the market, there is no product if you're the only vendor, he said. Pervasive and EnterpriseDB are going up against Oracle. We don't want to be in that space, we don't want to take the heat from Oracle. If you're working in a zoo you don't want to be the one who has to brush the teeth of the lion. And this just in (via another post on this list): http://www.prnewswire.com/cgi-bin/stories.pl?ACCT=104STORY=/www/story/10-07-2005/0004163873EDATE= http://www.oracle.com/innodb/index.html Oracle acquires Innobase, which is the company behind the InnoDB table bit of MySQL, i.e. the engine with the foreign keys, transactions and all that. Is there a shortage of lion toothpaste in Sweden or something? Ian Barwick ---(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] PostgreSQL 8.1 vs. MySQL 5.0?
On 10/7/05, Jim C. Nasby [EMAIL PROTECTED] wrote: On Thu, Oct 06, 2005 at 01:46:29PM -0500, Scott Marlowe wrote: On Thu, 2005-10-06 at 12:23, Jim C. Nasby wrote: (...) Are you aware of the MySQL Gotchas website (just google it)? Any time you see MySQL being stupid about something you should probably check there first to see if it's a feature. Oh yeah, very aware. What's amazed me is how often I find something that's majorly wrong that isn't in that list. For instance, this particular problem isn't on the gotcha page, although lots of other constraint issues are. Sadly, after talking to the author of the innodb table handler, I get the feeling this one isn't going to change. Please submit any missing items to the author. If he refuses them send them to me and I'll start an addendum. The author writes: all additions, corrections etc. most welcome. I haven't had a chance to update the site much recently, but I'm slowly going through the list to update it for MySQL 5. Ian Barwick ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Hash problem
On Sat, 12 Mar 2005 02:18:55 -0500, Madison Kelly [EMAIL PROTECTED] wrote: Hi all, I am back to trying to get the forum software called 'Ikonboard' working under postgres (it's advertised as being supported but the developers confirmed their pgSQL person left a while back). At the moment I am (well, the program is) getting trying to perform this query: SELECT * FROM tf_forum_posts WHERE FORUM_ID = HASH(0x868d4e0) AND TOPIC_ID = AND QUEUED = '0' ORDER BY POST_DATE LIMIT 2; which throws this error: ERROR: parser: parse error at or near x868d4e0 at character 53 Most of the problems so far have stemmed from changes from (I think) pgSQL 7.2 (the last version the program was actively tested on) to 7.4. I have yet to test it under 8.0. Does this seem like an obvious syntax error in the newer versions of pgSQL? If so, what is a valid substitution. This is a syntax error in Perl (assuming Ikonboard is a Perl app; it's hard to see from their homepage) of the kind you get when interpolating a hash reference where a scalar value is expected, e.g.: perl -e '%a = (); print \%a;' You'll need to find the offending section of the code and work out what's gone wrong; also TOPIC_ID seems to be missing a value. Possibly - and this is speculation - a preceding query has thrown an error which the app isn't dealing with (e.g. due to some change in PostgreSQL between 7.2 and 7.4, possibly a LIMIT x,y type clause or an attempt to insert a value which is longer than the database field etc.) and this is causing the insertion of invalid values in the above statement. Ian Barwick ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PostgreSQL still for Linux only?
On Wed, 9 Mar 2005 16:02:46 +1100, Edwin New [EMAIL PROTECTED] wrote: I don't want to split hairs, but wasn't Firebird originally Interbase? If so, you'll find it was originally a *nix product before it was a Windows database (back in the Ashton-Tate days for those with long memories). InterBase started on Apollo Domain, a spectacularly wonderful workstation with terrific networking. The initial release supported Apollo, Sun, HP/UX, VAX/VMS, Ultrix, and something else that escapes me. So, if you wonder 'was InterBase originally a Windows/DOS system?', the answer is 'no'. From: http://firebird.sourceforge.net/index.php?op=historyid=ann_2 (This page: http://firebird.sourceforge.net/index.php?op=historyid=ann_1 says also: InterBase started in the shower. Maybe the something else that escapes me was NetBSD? ;-) Ian Barwick ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] SCHEMA compatibility with Oracle/DB2/Firebird
On Sat, 22 Jan 2005 11:25:39 -0800, Chris [EMAIL PROTECTED] wrote: I know this isn't entirely postgresql specific, but it wouldn't be on another list either so here goes... I am writing an open source application where I would like to support at least oracle, and possibly firebird or DB2, in addition to postgresql which will be the default. I'm not going to try to support mysql. FWIW, Firebird doesn't have any form of schemas or cross-database query support (although I think commercial third-party extensions might exist for the latter). You'll probably be best off explicitly providing schema names for your common functions, e.g. SELECT * FROM common.mytable . Depending on your app, that could be better from a security point of view in PostgreSQL as well, if you want to prevent your users from sneakily replacing the common database objects. Ian Barwick ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Oracle and Postgresql Play Nice Together on Same Computer?
On Thu, 20 Jan 2005 00:03:28 -0500, Troyston Campano [EMAIL PROTECTED] wrote: Hello, I am an Oracle DBA and I want do a Postgresql 'proof of concept' at the large corporation where I work to test the benefits of using Postgresql in our environment. I want to install Postgresql onto a production server that currently runs Oracle. Are there any problems with running Postgresql and Oracle on the same machine? I mean, I've heard that the way Sybase and DB2 UDB are architected to handle memory hurts Sybase when DB2 UDB is installed on the same machine as the Sybase Server (something about UDB eating up all the memory and not giving it back to Sybase). Are there any issues running Postgresql and Oracle on the same machineanything special to know about memory, disk layout, and things like that? I just want to make sure the two engines play together on this same server. I had a hard time finding information about this via google. For testing purposes there shouldn't be any problems, at least in a *NIX environment. PostgreSQL is very undemanding and compared to Oracle is positively minuscule (at least as far as its installation footprint goes). I've run PostgreSQL, MySQL, DB2 and Oracle on the same development machine without any issues. Of course if another application is in constant use on a production server PostgreSQL won't perform as well as it could. Ian Barwick ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] How to manually insert an UTF-8 character into an SQL statement?
On Thu, 20 Jan 2005 14:48:40 +0100, Alban Hertroys
[EMAIL PROTECTED] wrote:
I'm trying to insert a record that contains an ô (o circumflex) into a
table using the psql client.
I also tried with phppgadmin and pgadmin, but both can't do this. They
insert a HTML entity and error out respectively. Not what I had in mind...
Supposedly I should be able to type:
INSERT INTO table (name) VALUES ('C\0x00f4te d''Azur');
but all I manage to achieve is inserting a capital 'C'...
It doesn't seem to matter to which encoding I set psql either.
What am I doing wrong?
For a start, 0x00F4 does not represent valid UTF-8; you want 0xC3B4.
AFAIK you can insert this using two different methods in psql:
\set myvalue '\'C\0xc3\0xb4te d\'\'Azur\''
INSERT INTO table (name) VALUES (:myvalue);
in 8.0 also:
\set myvalue '$$C\0xc3\0xb4te d\'Azur$$'
or:
INSERT INTO table (name) values
('C'||encode(decode('c3b4','hex'),'escape')||'te d''Azur');
Ian Barwick
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] PG8 final when
On Mon, 20 Dec 2004 13:32:57 +, Russ Brown [EMAIL PROTECTED] wrote: On Mon, 20 Dec 2004 14:13:40 +0100, Együd Csaba [EMAIL PROTECTED] wrote: Hi, as far as I can remember I somewhere read an article - maybe somwhere on a dbforum or the postgresql.org - which stated that after the RC1 the final version is required to be announced at around 15. dec. May be I'm misinformed. I definitely remember it being *estimated* for the 15th, but I don't recall anything being promised at all. from PostgreSQL Weekly News - December 7th 2004 ( http://archives.postgresql.org/pgsql-announce/2004-12/msg9.php ): ... The current plan is to make final release around December 15th if everything goes well. ... Ian Barwick ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] pl/pgsql oddity
On Thu, 16 Dec 2004 10:06:19 +0100 (CET), Joolz [EMAIL PROTECTED] wrote: Hello everyone, When writing some serverside code I ran into an oddity that I managed to boil down to this: --- create or replace function fubar() returns varchar as ' declare l integer; begin l = 38; if l 38 then return '' 38''; elseif l = 38 then Try elsif here. (No, I don't know what the problem with elseif is). Ian Barwick ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Oid to text...
On 22 Nov 2004 14:25:26 +0200, Katsaros Kwn/nos [EMAIL PROTECTED] wrote: Hi, Given the Oid restype of a Resdom object, is there any system table I could query in order to retrieve the text representation of this type? If for example a Resdom restype is 23 how can I get the string integer or something like that? Are these mappings stored anyware? pg_catalog.pg_type ? Ian Barwick ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Help with syntax for timestamp addition
On Mon, 22 Nov 2004 15:12:26 +0100, Patrick Fiche [EMAIL PROTECTED] wrote: Have a try at this syntax SELECT number FROM procedures WHERE date + CAST( numdays || ' days' AS interval ) = CURRENT_TIMESTAMP; Just for the record you could write it like this too: SELECT number FROM procedures WHERE date + (numdays || ' days')::interval = CURRENT_TIMESTAMP; Ian Barwick ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Transactions in different DB
On Tue, 16 Nov 2004 11:22:27 +0100, Stefano Farina [EMAIL PROTECTED] wrote: Hi all I have to develop an embedded database using memory FLASH and memory RAM Some tables must be saved in RAM and some tables in FLASH ( RAM and FLASH are two different mounted directories in the file system). I saw it's possible to save different databases in different directories with CREATE DATABASE options. It's possible to save only some tables in different directories?? Beginning with PostgreSQL 8.0, yes - this provides tablespaces. If I want to work with two different databases, it's possible to do a single transaction on two different databases ?? Not AFAIK, though contrib/dblink might be able to help you. HTH Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Max length name of a database/schema
On Mon, 1 Nov 2004 10:20:14 +0100, Martijn van Oosterhout [EMAIL PROTECTED] wrote: All the same, there is only one identifier type. It got bigger recently, but it's either 31 or 63 characters... Look through the documentation. It's been 63 characters since 7.2 or 7.3. See src/include/postgres_ext.h and look for NAMEDATALEN. Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Calling on all SQL guru's
On Mon, 1 Nov 2004 09:59:44 -0800, John Fabiani [EMAIL PROTECTED] wrote: Hi, First I'm trying to move a MySQL database to Postgres. I have to emulate a MySQL sql statement - ''Describe tablename' which in general is '\d tablename' from psql. If I use '-E' my 7.3.x provides three sql statements and by 7.4.x produces four statements. But what I want is a single SQL statement that produces the following: -- fieldname | field type | isPK --- clientidint true lastcharfalse first charfalse Unfortunately the guru certificate is still in the post, but below is a nasty kludge which might be going in the general direction you want: SELECT c.column_name AS fieldname, c.data_type AS fieldtype, COALESCE(i.indisprimary,FALSE) AS is_pkey FROM information_schema.columns c LEFT JOIN information_schema.key_column_usage cu ON (c.table_name=cu.table_name AND c.column_name=cu.column_name) LEFT JOIN pg_class cl ON(cl.relname=cu.table_name) LEFT JOIN pg_index i ON(cl.oid= i.indrelid) WHERE c.table_name='insert_tablename_here' Caveats: - this is _not_ schema-aware. - requires the information schema, e.g. 7.4 and later - might just be horribly wrong anyway, but you get the general idea ;-) HTH Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] counting records of schema
On Tue, 26 Oct 2004 08:03:26 +0200, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I need to know how many records are in a specific schema of a database. I've tried with a function but there are still problems Providing details of the problems usually helps ;-). Can you give me some hints : -- Function: count_records(myschema varchar) -- DROP FUNCTION count_records(varchar); CREATE OR REPLACE FUNCTION count_records(varchar) RETURNS int8 AS $BODY$DECLARE anzahl bigint := 0; summe bigint := 0; ds RECORD; tabellenname varchar(100); BEGIN FOR ds IN select * from pg_tables where schemaname = myschema LOOP 'myschema' is not defined anywhere - I presume it's the alias for the function's VARCHAR argument? tabellenname := quote_ident(ds.schemaname) || '.' || quote_ident(ds.tablename); EXECUTE 'SELECT count(*) FROM ' || tabellenname INTO quote_ident(anzahl); You can't (directly) extract the result of a dynamically-created SELECT using EXECUTE, see http://www.postgresql.org/docs/7.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN for further information and hints. summe := summe + anzahl; END LOOP; return summe; END;$BODY$ LANGUAGE 'plpgsql' VOLATILE; BTW you don't need to specify VOLATILE here - it's the default - and STABLE might be the appropriate choice anyway. HTH Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] having clause question
On Sat, 30 Oct 2004 15:17:16 -0700, Shane Wegner [EMAIL PROTECTED] wrote: Hello, (...) I want to retrieve any last names with more than 1 occurence in the table. Under MySQL, this query does the trick. select lastname,count(*) as c from names group by lastname having c 1; But under PG, it errors out ERROR: column c does not exist Is it possible to do a query like this with PostgreSQL? select lastname,count(*) as c from names group by lastname having count(*) 1; HTH Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] compatibilityissues from 7.1 to 7.4
On Tue, 26 Oct 2004 18:22:55 +0900, Joel [EMAIL PROTECTED] wrote: I seem to remember reading a post on this, but searching marc does not seem to bring it up immediately. Company BBS is on postgresql, but it's still at 7.1. The guy in charge of it wants some ballpark estimates and warnings about upgrading to 7.4 so he doesn't have to worry about the recent vulnerabilities. War stories? Things to watch out for? Off the top of my head: over-length data inserted into varchar fields will no longer be silently truncated, raising an error instead ( a big source of problems with web-based apps); also, the LIMIT x,y syntax will no longer work. Your best bet is fro someone who knows your system to go through the PostgreSQL release notes. Ian Barwick ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] unicode and varchar
On Fri, 10 Sep 2004 14:57:18 +0200, Michael Wimmer [EMAIL PROTECTED] wrote: (...) Is there a way to make this work in 7.4.x too? May problem is that we are in a rush to do a port of our application from informix and I will not be able to wait until 8.0 will be released. What encoding does the 7.4.x database have? Ian Barwick ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Confused with db client encoding
On Mon, 06 Sep 2004 00:02:24 +0100, Carlos Correia [EMAIL PROTECTED] wrote:
Hi,
Here is the output a psql session. Please notice that the identation
inconsistences in the records containg non ASCII chars is as outputed by
psql.
The db was created with LANIN9 and the console was ran (in the same
machine) using UTF-8 (my system's default).
I was surprised to notice that setting the client to unicode (which is
what that console is using) messed the localized chars as I was
expecting to see the opposite way.
On the other way, when invoking from a Java app, running on the same
machine, the accentuaded chars also appeared messed.
(...)
3 | Tx. Dinheiro | Transacções a Dinheiro
11 | Nota de Crédito | Notas de Crédito
12 | Nota de Débito | Notas de Débito
21 | G. Remessa | Guia de Remessa
It looks like this data was entered as UTF-8 but the client encoding
was LATIN9 (or whatever), meaning the two incoming bytes from each
accentuated character in UTF-8 was interpreted by the backend as two
individual bytes in LATINx.
Test case (session in a UTF-8 environment):
test=# CREATE DATABASE ctest encoding 'LATIN1';
CREATE DATABASE
test=# \c ctest;
You are now connected to database ctest.
ctest=# CREATE TABLE coding (data TEXT);
CREATE TABLE
ctest=# SET client_encoding TO LATIN1;
SET
ctest=# INSERT INTO coding VALUES('müller');
INSERT 349960 1
ctest=# SELECT * FROM coding;
data
-
müller
(1 row)
ctest=# SET client_encoding TO UNICODE;
SET
ctest=# SELECT * FROM coding;
data
-
müller
(1 row)
Ian Barwick
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] UTF-8 and LIKE vs =
On Tue, 24 Aug 2004 00:46:50 +0200, Markus Bertheau [EMAIL PROTECTED] wrote: , 23.08.2004, 23:04, David Wheeler : On Aug 23, 2004, at 1:58 PM, Ian Barwick wrote: er, the characters in name don't seem to match the characters in the query - '' vs. '' - does that have any bearing? Yes, it means that = is doing the wrong thing!! The collation rules of your (and my) locale say that these strings are the same: [EMAIL PROTECTED] markus]$ cat t [EMAIL PROTECTED] markus]$ uniq t [EMAIL PROTECTED] markus]$ wild speculation in need of a Korean speaker, but: [EMAIL PROTECTED]:~/tmp cat j.txt [EMAIL PROTECTED]:~/tmp uniq j.txt All but the first and last lines are random Korean (Hangul) characters. Evidently our respective locales think all Hangul strings of the same length are identical, which is very probably not the case... Ian Barwick ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] UTF-8 and LIKE vs =
On Mon, 23 Aug 2004 16:50:04 -0700, David Wheeler [EMAIL PROTECTED] wrote: On Aug 23, 2004, at 4:34 PM, Ian Barwick wrote: wild speculation in need of a Korean speaker, but: [EMAIL PROTECTED]:~/tmp cat j.txt [EMAIL PROTECTED]:~/tmp uniq j.txt All but the first and last lines are random Korean (Hangul) characters. Evidently our respective locales think all Hangul strings of the same length are identical, which is very probably not the case... Does this go away if you change your locale to C? Yes. Ian Barwick ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] estimating table size
On Mon, 26 Jul 2004 18:14:06 -0400, David Parker [EMAIL PROTECTED] wrote: Given a table, foo, created in a database but not populated, is there a procedure that will return an estimate of the size of a given tuple in that table? It looks like pgstattuple reports on actual table pages; I'm looking for something that reads the lengths of each row, and knows what the storage overhead would be (extra 4 bytes for varchar, for instance). Alternatively, is there documentation somewhere that can give me a good rule of thumb for this? I can add up the lengths of the columns and make my own judgements about averages for varchar columns. The hidden storage overhead is what I'm wondering about, I guess. There is a little info in the FAQ: http://www.postgresql.org/docs/faqs/FAQ.html particularly sections 4.6 and 4.14 Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] nmap not showing postgres
On Sun, 18 Jul 2004 15:30:01 +0200, Ian Barwick [EMAIL PROTECTED] wrote: On Tue, 13 Jul 2004 05:53:36 +0100 (BST), raj veluchamy [EMAIL PROTECTED] wrote: hello all, i am running postgres in compiling postgres in OSCAR cluster. installation is done to /home/pgsql as i couldn't do it at /var/lib. while i nmap, the postgres is not shown, but when i give createdb, this is working and i could work on with the data base. I'm guessing you want to ascertain whether PostgreSQL is listening on a TCP/IP port? I'm not familiar with OSCAR so I don't know whether it has any impact on PostgreSQL operations. A command like netstat -t should show you what services are listening on what ports. Forgot to add: by default PostgreSQL doesn't listen on a TCP/IP port; you have to specify this when starting the server. Ian Barwick ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Pg + perl + apache
On Saturday 14 February 2004 22:35, Michael L. Artz wrote: (...) If anyone has seen anything like this before (i.e. random errors in a similar apache/mod_perl) situation, or might have any pointers as to where to look next, let me know. What would cause postgres to return an error for a selectrow_array, or one of those others mentioned? Just checking, but do all your scripts have : use strict; use warnings; at the top? Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Buglist
On Tuesday 19 August 2003 23:10, scott.marlowe wrote: On 19 Aug 2003, Bo Lorentsen wrote: On Tue, 2003-08-19 at 18:17, Vivek Khera wrote: Since the beginning of time (at least MySQL v3.22) MySQL has silently ignored the foreign key references in table create statement. Now that they have foreign key support (version 4.x), do they honor those statements? Nope. You have to use their own syntax to declare your FKs. They still silently ignore the references in the table create statements. Is this really true ?? Does 4.x still not support FK, then how about transactions, does they that not work too ? Is this not just the MyISAM tables that still got the problem (they are verison 3.x) ? No, the problem is that in SQL spec, you do it with the foreign key declaration inside parnes in the create statement like: create table abc123 ( id serial unique, info text); create table abc1234 ( moreinfo text, ref_id int, foreign key (ref_id) references abc123(id) on delete cascade ); In MySQL this syntax is silently swallowed, while their own proper syntax is like this: create table abc123 ( id serial unique, info text) type=innodb; create table abc1234 ( moreinfo text, ref_id int) foreign key (ref_id) references abc123(id) on delete CASCADE type=innodb; (To be precise this will fail with an obscure message; an index must be created on ref_id) So the syntaxes are different, and one is apparently swallowed without error or anything, but in fact you have no fks in place. Just to confuse things further: 1: if the MySQL version running is not configured for innodb tables, tables created with type=innodb will be silently converted to MyISAM; 2: These statements will succeed: create table abc123 ( id INT unique, info text ) type=innodb; create table abc1234 ( moreinfo text, ref_id int REFERENCES abc123(id) ) type=innodb; but the foreign key defined on ref_id is (I presume) transported to a remote forest in Sweden and eaten by goats ;-) Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Why lower's not accept an AS declaration ?
On Monday 18 August 2003 18:59, Hervé Piedvache wrote: Hi, An to be more precise what I exactly want to do : select case when 'now' between t.begin and t.end then t.login else 'None' end as log from my_table t order by lower(log); How about something like: select case when 'now' between t.begin and t.end then t.login else 'None' end as log, LOWER(case when 'now' between t.begin and t.end then t.login else 'None' end) as log_lower from my_table t order by 2; Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Auto completion in psql
On Tuesday 29 July 2003 17:58, Francisco Reyes wrote: How does line completion gets to psql? At my FreeBSD machines when I build the PostgreSQL port I have always had line completion. Now I need to do some work on a Linux SUSE machine (which I don't administer) and psql doesn't have line completion. The person that manages the machine installed from sources. It needs to be (re)compiled with readline support, and you will need the readline headers (SuSE provides a readline-devel rpm). Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] migrating data from 7.3.x down to 7.2.x
On Tuesday 15 July 2003 09:51, Stefan Armbruster wrote: Hi, Am Mon, 2003-07-14 um 21.01 schrieb Ian Barwick: On Monday 14 July 2003 16:04, Stefan Armbruster wrote: Hi, I tried to migrate a database from 7.3.2 down to PostgreSQL 7.2.2 Any particular reason? Yes, I'm developing with 7.3.2, production is 7.2.2. Not hostsharing.net by any chance? (...) In other words: a short script with some cut replace operations could do the job? Yes, although depending on your data it might take a bit of trial and error. Is there a specific document describing all the DDL changes from 7.2 to 7.3?ßß The release notes: http://www.postgresql.org/docs/7.3/static/release.html#RELEASE-7-3 are usually a good starting point. Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Firebird vrs Postgresql
On Wednesday 16 July 2003 00:32, Andrew Gould wrote: 4. I couldn't find the documentation to tell me where to start once I installed the database server. (This may have been obvious to a DBA.) Here: http://www.ibphoenix.com/main.nfs?a=ibphoenixpage=ibp_download there is a Firebird Quick Start Guide which provides useful orientation for the uninitiated (or those merely poking about ;-). Note that the docs are for 1.0x, some things described seem to have changed in the 1.5 betas. (Regarding #3: What is the proper way to refer to Unix, Linux, FreeBSD, etc., collectively?) Unixen, or possibly GNU/Unixen ? ;-) Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Accent insensitive search
On Tuesday 01 July 2003 17:11, Alejandro Javier Pomeraniec wrote: Hi ! Does anyone knows how to make accent insensitive searches?? For example i have this data in a table Colón Polo I need that this query SELECT * FROM testtable WHERE testfield like '%olo%'; brings both results instead of only showing Polo. One solution[*]: SELECT * FROM testtable WHERE to_ascii(testfield,'LATIN1') LIKE '%olo%' Note this might not work with all database encodings, especially UNICODE. [*] no doubt someone will be along in a moment with another. Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Duplicate key insert question
On Wednesday 02 July 2003 02:58, Jean-Christian Imbeault wrote: (B Alvaro Herrera wrote: (B No, only the "second" one will fail (though it's difficult which one is (B the second) (B (B From: (B (B http://marc.theaimsgroup.com/?l=postgresql-generalm=105656988915991w=2 (B (B Ian Barwick wrote: (B (B [...] (B (B I proposed that same solution 3 years ago. Tom shoots it down: (B (B(This quote is not from Mike Mascari, not me) (B (B [...] (B (B I couldn't get the link to work so I couldn't read why Tom shot it down. (B But if Tom shot down this idea down ... then it mustn't be correct. (B (Bsee: (Bhttp://archives.postgresql.org/pgsql-general/2000-12/msg00970.php (B (Bentire thread: (Bhttp://archives.postgresql.org/pgsql-general/2000-12/msg00947.php (B (B (BIan Barwick (B[EMAIL PROTECTED] (B (B (B---(end of broadcast)--- (BTIP 6: Have you searched our list archives? (B (B http://archives.postgresql.org
Re: [GENERAL] adding fields to a table
On Thursday 26 June 2003 18:34, Benjamin Jury wrote: (...) You can use ALTER TABLE. ALTER TABLE table ADD [column] column type Can also rename columns, add constraints, change to NOT NULL, etc. However you cannot currently remove a column... Oh yes you can: ALTER TABLE tbl DROP COLUMN whatever http://www.postgresql.org/docs/view.php?version=7.3idoc=0file=ddl-alter.html IIRC it was introduced fairly recently (7.3?) Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] timestamp() broken in 7.2.4?
On Friday 27 June 2003 00:47, Holger Marzen wrote: In 7.1.3 I can use select timestamp(date '2001-01-01', time '00:00'); but in 7.2.4 I get db1=# select timestamp(date '2001-01-01', time '00:00'); ERROR: parser: parse error at or near date changed in 7.2, see: http://www.postgresql.org/docs/view.php?version=7.3idoc=0file=release-7-2.html The timestamp(), time(), and interval() functions are no longer available. Instead of timestamp(), use timestamp 'string' or CAST. - something like: select timestamp '2001-01-01 00:00'; should work Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] INSERT WHERE NOT EXISTS
On Wednesday 25 June 2003 20:06, Reuben D. Budiardja wrote: Hi, I am developing application with PHP as the front end, PGSQL as the backend. I am trying to figure out what's the best way to do this. I want to check if an entry already exists in the table. If it does, then I will do UPDATE tablename otherwise, I will do INSER INTO tablename... (...) I vaguely remember in Oracle, there is something like this: INSERT INTO mytable SELECT 'value1', 'value2' FROM dummy_table WHERE NOT EXISTS (SELECT NULL FROM mytable WHERE mycondition) This query will do INSERT, if there is not an entry already in the TABLE mytable that match the condition mycondition. Otherwise, the INSERT just fails and return 0 (without returning error), so I can check on that and do update instead. This kind of query should work; just leave out the FROM dummy_table bit. (in Oracle it would be FROM dual). Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] INSERT WHERE NOT EXISTS
On Wednesday 25 June 2003 21:37, Mike Mascari wrote: Ian Barwick wrote: On Wednesday 25 June 2003 20:06, Reuben D. Budiardja wrote: (...) This kind of query should work; just leave out the FROM dummy_table bit. (in Oracle it would be FROM dual). I proposed that same solution 3 years ago. Tom shoots it down: http://groups.google.com/groups?hl=enlr=ie=UTF-8oe=UTF-8threadm=3A4D611 6.1A613402%40mascari.comrnum=1prev=/groups%3Fq%3DMike%2BMascari%2BINSERT%2 BNOT%2BEXISTS%26ie%3DUTF-8%26oe%3DUTF-8%26hl%3Den Reuben must be prepared for unique key violation, I'm afraid. And, despite the optimism in the link, we still don't have savepoints. :-( aha, useful to know. Thanks. Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] query help
Jeff Patterson wrote: This seems like such a basic function that I'm sure I am missing something fundamental. I have a table, say xref, whose columns are primary key values for other tables. Some of theses keys may be NULL for a given row in xref. I want to create a query that returns the corresponding entries in the other tables. xref: drug_id | function_id|syst_id |text_id -- d0001 | 2 | 3| 3423 d0001 | 5 || 5678 d0056 | 3 | 5| system_id: syst_id | syst_desc --- 3 | renal 4 | hepatic 5 | respiratory clinical_text: text_id| clinical_text --- 3423 | 'some medical mumbo jumbo' 5678 | 'more of the same' I want the syst_desc and clinical_text (plus other similar data from tables not shown) given a drug_id and function_id. Any help? If I understand you correctly, you want the query to return the syst_desc and clinical_text fields where either or both are available? (and not just where both are available)? If so a LEFT JOIN may be your best friend. The statement could look like this: SELECT system_id.syst_desc, clinical_text.clinical_text FROM xref LEFT JOIN system_id ON xref.syst_id=system_id.syst_id LEFT JOIN clinical_text ON xref.text_id=clinical_text.text_id WHERE xref.drug_id=? - insert query value here AND xref.function_id=? - and here (disclaimer: statement untested) The values not available will be returned as NULL. Thanks a bunch, a bunch of what? ;-) HTH Ian Barwick -- Ian Barwick - Developer http://www.akademie.de Remove SUNGLASSES to reply ;-) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
