[GENERAL] Hard upgrade (everything)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi list, I've asked this question already on the PostGIS list, but I think it might get great answers here too. I'm running two database cluster with PostGIS 1.5 and PostgreSQL 9.1 on FreeBSD 9.2-RELEASE-p3 and apparently my PostGIS and PostgreSQL are a little bit outdated. Now the plan is to upgrade both PostGIS to 2.1 and PostgreSQL to 9.3, which are the latest versions in the ports. Now, I am a little bit lost with all the precautions and measures one has to undertake to do the hard upgrade. From what I read on the PostgreSQL site, I can choose whether I want to use pg_upgrade (without the need of a intermediate dump) or pg_dumpall to put the data aside. I presume I can't use pg_upgrade since this wouldn't take PostGIS into account, right? That leaves me with pg_dumpall for the PostgreSQL upgrade. Now, reading the PostGIS instructions to upgrade I come to the conclusion that a binary dump is required to put the data aside while doing the upgrade. Thing is pg_dump only dumps one spatial database, and I have several in my clusters, so I'd need to dump all of them, right? And here's where my confusion starts, since there are different tools used for PostgreSQL and PostGIS for the individual upgrade. What would be the correct procedure to dump a complete cluster in a PostGIS and PostgreSQL compliant way? My ideas so far: Step one: Use pg_dumpall to dump the roles and cluster metadata Step two: Iterate and use pg_dump in binary mode to dump every database in the cluster Step three: rename/empty the target drive/folder Step four: do the upgrade of PostgreSQL and PostGIS Step five: restore the roles and metadata Step six: use the command utils/postgis_restore.pl to restore each individual database Does that look sound? On a side note, I tried upgrading each part individually, but the port dependencies won't let me do that because upgrading PostgreSQL to 9.3 will also pull PostGIS 2.1 and upgrading PostGIS 2.1 will also pull PostgreSQL 9.3, so I only get the two of them ... Any tips on the procedure are greatly welcome :-) Frank - -- Frank BRONIEWSKI METRICO s.à r.l. géomètres technologies d'information géographique rue des Romains 36 L-5433 NIEDERDONVEN tél.: +352 26 74 94 - 28 fax.: +352 26 74 94 99 http://www.metrico.lu -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQEcBAEBAgAGBQJS80FVAAoJEHopqW0d1cQVjzMIAJQ6povfOSYg0NIw5DXF0KlZ 0MQQjwoOwRIPrCkllwDEpmioI2OtkBH03LYuQQYi8SArovtBRlTwyvZsUgFuuxy6 qAQhHcGxLyJPvxBIXVRhqnmn89a1otGxXTI+ZedNbJsj74vW0h29KRBAyklphe/C iAGw8+2zr0yiBLJdHWZvnMdS0PkL4jc7UY1XfmCg3AvNQU1EgiUYdWOEn26fqj0g bXrpHERgv8c+Hk8r8/G4WRD6rC0aMirB0lynxn+FHhSc9mzXUbDbER99M06vXrtF uIIeOTfr/Pu5eyjHDc3stg2LAtoNTvnvvJ0S+5Shi6ndLRy3P7AHZ6y915AMkRA= =4KIY -END PGP SIGNATURE- -- 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] Temporary table already exists
Hi Adrian,
it is not an artifact. This log comes from a multiplayer game, and this is
an specific test to replicate the error. Practically, there are two users
that execute the same operation, so you can see the simultaneous selects.
My opinion was every session was isolated from others and temporary table
was atomic for every session (transaction).
But I think that I'm not true.
Are The two selects in the same session in my case? Why?
Thanks in advance.
Mephysto
On 6 February 2014 04:40, Adrian Klaver-3 [via PostgreSQL]
ml-node+s1045698n5790784...@n5.nabble.com wrote:
On 02/05/2014 12:19 PM, Mephysto wrote:
I posted my last message via Nabble, so I think that the log is not
shown in email.
I try to repost my log via email:
DEBUG: building index pg_toast_148085_index on table
pg_toast_148085
CONTEXT: SQL statement CREATE LOCAL TEMPORARY TABLE deck_types
ON COMMIT DROP
AS
SELECT
stored_functions_v0.get_card_deck_types(t1.id_master_card) AS deck_type_ids
FROM ccg_schema.deck_composition T0
,ccg_schema.cards_per_user T1
WHERE id_deck = p_id_deck
AND t1.id_owner = l_id_user
AND t0.id_card = t1.id_card
PL/pgSQL function stored_functions_v0.get_deck_types(bigint)
line 12 at SQL statement
STATEMENT: SELECT * FROM
stored_functions_v0.get_deck_master_properties($1)
LOG: execute unnamed: SELECT typname FROM pg_catalog.pg_type WHERE
oid = $1
DETAIL: parameters: $1 = '1016'
LOG: execute unnamed: SET application_name = ''
LOG: execute unnamed: SELECT e.typdelim FROM pg_catalog.pg_type t,
pg_catalog.pg_type e WHERE t.oid = $1 and t.typelem = e.oid
DETAIL: parameters: $1 = '1016'
LOG: execute unnamed: INSERT INTO admin.logs VALUES ('', '2014-02-05
16:15:13.249', 'it.redevogames.redevolib.classes.PgStoredExecutor',
'DEBUG', $$Executing SELECT * FROM
stored_functions_v0.get_deck_from_id_user(?)$$,
'PgStoredExecutor.java:215', $$Executing SELECT * FROM
stored_functions_v0.get_deck_from_id_user(?)
$$)
LOG: execute unnamed: SELECT * FROM
stored_functions_v0.get_deck_from_id_user($1)
DETAIL: parameters: $1 = '51'
LOG: execute unnamed: SET application_name = ''
LOG: execute unnamed: INSERT INTO admin.logs VALUES ('', '2014-02-05
16:15:13.258', 'it.redevogames.redevolib.classes.PgStoredExecutor',
'DEBUG', $$Executing SELECT * FROM
stored_functions_v0.get_deck_master_properties(?)$$,
'PgStoredExecutor.java:215', $$Executing SELECT * FROM
stored_functions_v0.get_deck_master_properties(?)
$$)
LOG: execute unnamed: SELECT * FROM
stored_functions_v0.get_deck_master_properties($1)
DETAIL: parameters: $1 = '1'
ERROR: relation deck_types already exists
CONTEXT: SQL statement CREATE LOCAL TEMPORARY TABLE deck_types
ON COMMIT DROP
AS
SELECT
stored_functions_v0.get_card_deck_types(t1.id_master_card) AS deck_type_ids
FROM ccg_schema.deck_composition T0
,ccg_schema.cards_per_user T1
WHERE id_deck = p_id_deck
AND t1.id_owner = l_id_user
AND t0.id_card = t1.id_card
PL/pgSQL function stored_functions_v0.get_deck_types(bigint)
line 12 at SQL statement
STATEMENT: SELECT * FROM
stored_functions_v0.get_deck_master_properties($1)
ERROR: current transaction is aborted, commands ignored until end of
transaction block
Not sure where I am going at this point, just trying to understand.
If I follow correct CREATE LOCAL TEMPORARY TABLE deck_types is inside
the function stored_functions_v0.get_deck_types() which in turn is being
called by stored_functions_v0.get_deck_master_properties().
Is this correct or not?
Also why in the data being logged to admin.logs are the $$Executing
SELECT * statements repeated, are there really simultaneous SELECTs or
is that an artifact of the logging?
Thanks in advance.
Mephysto
--
Adrian Klaver
[hidden email] http://user/SendEmail.jtp?type=nodenode=5790784i=0
--
Sent via pgsql-general mailing list ([hidden
email]http://user/SendEmail.jtp?type=nodenode=5790784i=1)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
If you reply to this email, your message will be added to the discussion
below:
http://postgresql.1045698.n5.nabble.com/Temporary-table-already-exists-tp5789852p5790784.html
To unsubscribe from Temporary table already exists, click
herehttp://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_codenode=5789852code=bWVwaHlzdG9vbmhlbGxAZ21haWwuY29tfDU3ODk4NTJ8LTkwNDU5Mzg0Ng==
.
Re: [GENERAL] Ordering Results by a Supplied Order
On 02/06/2014 04:16 AM, Michael Sacket wrote: Often times I find it necessary to work with table rows in a specific, generally user-supplied order. It could be anything really that requires an ordering that can't come from a natural column. Most of the time this involved manipulating a position column from the client application. In any case, I've often found that to be cumbersome, but I think I've come up with a solution that some of you may find useful. Up until 9.4, that's a good way to do it. Starting from 9.4, you can use the WITH ORDINALITY feature. http://www.postgresql.org/docs/devel/static/sql-select.html -- Vik
Re: [GENERAL] How to get rid of superfluous WAL segments?
On 02/06/2014 06:51 AM, Torsten Förtsch wrote: On 06/02/14 06:46, Torsten Förtsch wrote: we decreased wal_keep_segments quite a lot. What is the supposed way to get rid of the now superfluous files in pg_xlog? Nothing special. The database did it for me. It cleans up after a checkpoint. If you've got a slow server and don't want to wait for checkpoint_timeout (default 5 minutes), you can issue a CHECKPOINT command yourself. Under no circumstances should you ever delete files from pg_xlog by hand. -- Vik -- 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] Temporary table already exists
@mephysto I think you are trying to solve the wrong type of problem. Creation of tables (temporary or not) are not supposed to run concurrently. So, this is not an issue of PostgreSQL but design. There are two ways to solve the problem. a) You could use the sessionID (provided The Glassfish server) to create unique names for the temporary table, if the temporary table is unique per session. If not, since you are talking about multiplayer game the temporary table name could include the userID, so it is unique per user. In that sense, it could be persistent per user, or temporary depending on your needs. In that case you will need EXECUTE command to CREATE the table in your pgsql code, since the table name must be a variable in your functions. b) I highly suspect that this temporary table is used for either caching or local sorting / ordering. In this case Postgres is again the wrong tool. You can use an embedded DB like SQLite, HSQLDB for the local database which may be used for this type of operations, which 'syncs' to the global PostgreSQL DB when connecting or disconnecting. Every client will have a separate copy of this DB, so no overhead to the server. Either way you will have solved your issue. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Temporary-table-already-exists-tp5789852p5790806.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] Ordering Results by a Supplied Order
Hey, I dont understand the difference between this ORDINALITY option and adding a row_number() over() in the SELECT. Thanks, Cheers, Remi-C 2014-02-06 Vik Fearing vik.fear...@dalibo.com: On 02/06/2014 04:16 AM, Michael Sacket wrote: Often times I find it necessary to work with table rows in a specific, generally user-supplied order. It could be anything really that requires an ordering that can't come from a natural column. Most of the time this involved manipulating a position column from the client application. In any case, I've often found that to be cumbersome, but I think I've come up with a solution that some of you may find useful. Up until 9.4, that's a good way to do it. Starting from 9.4, you can use the WITH ORDINALITY feature. http://www.postgresql.org/docs/devel/static/sql-select.html -- Vik
Re: [GENERAL] Hard upgrade (everything)
I would use normal pg_dump and pg_restore for the DBs and not utils/postgis_restore.pl. Also, AFTER I backup all databases and everything else, you could try to upgrade Postgis without upgrading PostgreSQL by buliding from source e.g. http://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS21Ubuntu1304src. There it says that Postgis 2.1 could be used with PostgreSQL 9.1. This will give you access to Postgis 2.1 features without reinstalling everything. Of course normally I would not upgrade if this is an 1-2 years project, unless I 100% need Postgis 2.1 features not present in 1.5. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Hard-upgrade-everything-tp5790801p5790811.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] Hard upgrade (everything)
On my private computer I upgraded first the postgres to 9.3, then upgraded postgis. Sadly according to http://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS , postgis 1.5 is not compatible with postgres 9.3. However POstgis 2.1 is compatible with you current postgres option. So as suggested you can upgrade postgis (see hard/soft upgrade), the upgrade postgres. By the way postgis is very easy to compil with ubuntu (use package system to get dependecies, then simply sudo ./autogen.sh, sudo ./configure, make , sudo make install) Cheers, Remi-C 2014-02-06 alexandros_e alexandros...@gmail.com: I would use normal pg_dump and pg_restore for the DBs and not utils/postgis_restore.pl. Also, AFTER I backup all databases and everything else, you could try to upgrade Postgis without upgrading PostgreSQL by buliding from source e.g. http://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS21Ubuntu1304src. There it says that Postgis 2.1 could be used with PostgreSQL 9.1. This will give you access to Postgis 2.1 features without reinstalling everything. Of course normally I would not upgrade if this is an 1-2 years project, unless I 100% need Postgis 2.1 features not present in 1.5. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Hard-upgrade-everything-tp5790801p5790811.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] Ordering Results by a Supplied Order
On 02/06/2014 10:00 AM, Rémi Cura wrote: Hey, I dont understand the difference between this ORDINALITY option and adding a row_number() over() in the SELECT. WITH ORDINALITY will give you something to order by. You should never do row_number() over () because that will give you potentially random results. -- Vik -- 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] Temporary table already exists
I don't need local sorting, I only had to retrieve some objects from db belongs to user. A this point is it better unlogged tables or postgres object arrays? Il 06/feb/2014 09:35 alexandros_e [via PostgreSQL] ml-node+s1045698n5790806...@n5.nabble.com ha scritto: @mephysto I think you are trying to solve the wrong type of problem. Creation of tables (temporary or not) are not supposed to run concurrently. So, this is not an issue of PostgreSQL but design. There are two ways to solve the problem. a) You could use the sessionID (provided The Glassfish server) to create unique names for the temporary table, if the temporary table is unique per session. If not, since you are talking about multiplayer game the temporary table name could include the userID, so it is unique per user. In that sense, it could be persistent per user, or temporary depending on your needs. In that case you will need EXECUTE command to CREATE the table in your pgsql code, since the table name must be a variable in your functions. b) I highly suspect that this temporary table is used for either caching or local sorting / ordering. In this case Postgres is again the wrong tool. You can use an embedded DB like SQLite, HSQLDB for the local database which may be used for this type of operations, which 'syncs' to the global PostgreSQL DB when connecting or disconnecting. Every client will have a separate copy of this DB, so no overhead to the server. Either way you will have solved your issue. -- If you reply to this email, your message will be added to the discussion below: http://postgresql.1045698.n5.nabble.com/Temporary-table-already-exists-tp5789852p5790806.html To unsubscribe from Temporary table already exists, click herehttp://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_codenode=5789852code=bWVwaHlzdG9vbmhlbGxAZ21haWwuY29tfDU3ODk4NTJ8LTkwNDU5Mzg0Ng== . NAMLhttp://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewerid=instant_html%21nabble%3Aemail.namlbase=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespacebreadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml -- View this message in context: http://postgresql.1045698.n5.nabble.com/Temporary-table-already-exists-tp5789852p5790819.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: [GENERAL] client encoding that psql command sets
On 02/05/2014 11:43 PM, 坂本 翼 wrote: Hi all, When I tried to psql command, I found that the result of command seems to be different from explanation of the manual. Please tell me which is correct performance, the result of command or the explanation of manual. I show the details as follows. [Events］ The encoding that psql command sets has difference between the result of command and the explanation of manual. The following is the explanation of encoding that psql command sets, which I made extracts from the 9.2 manual. --- VI. Reference / II. PostgreSQL Client Applications / psql / Connecting to a Database If at least one of standard input or standard output are a terminal, then psql sets the client encoding to auto, which will detect the appropriate client encoding from the locale settings (LC_CTYPE environment variable on Unix systems). If this doesn't work out as expected, the client encoding can be overridden using the environment variable PGCLIENTENCODING. Above explanation says that If at least one of standard input or standard output are a terminal, then psql sets the client encoding to auto, which will detect the appropriate client encoding from the locale settings (LC_CTYPE environment variable on Unix systems). But according to the result from psql command, it seems that the appropriate client encoding is NOT detected from the locale settings. The result of psql command is following. [Precondition of test] - PostgreSQL VL：9.2.4 - Locale settings encoding : ja_JP.eucJP (LANG) Not sure that it makes a difference but the docs say psql looks at LC_CTYPE not LANG for Unix systems. You did not say what OS you are working on though from the examples I am guessing some form of Unix. - Client encoding：UTF8 - client_encoding(paramater of postgresql.conf) : None specified - test.txt is psql's input file which is written \encoding. [Result] 1) In case both standard input and standard output are a terminal $ psql postgres -f test.txt -o result.txt EUC_JP → Locale setting encoding 2) In case only standard input is a terminal $ psql postgres -f test.txt result.txt $ cat result.txt UTF8 →Client encoding 3) In case only standard output is a terminal $ psql postgres -o result.txt test.txt UTF8 →Client encoding 4) In case both standard input and standard output are not a terminal $ psql postgres test.txt result.txt $ cat result.txt UTF8 →Client encoding 1) and 4) performed just as manual, but 2) and 3) do not. If 2) and 3) performed just as munual, I think that their encoding should be EUC_JP that is locale setting encoding. If manual has mistake, I think that correct explanation is following. If BOTH standard input AND standard output are a terminal, then psql sets the client encoding to auto, which will detect the appropriate client encoding from the locale settings (LC_CTYPE environment variable on Unix systems). What do you think? Warmest Regards. -- Adrian Klaver adrian.kla...@gmail.com -- 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] Temporary table already exists
On 02/06/2014 12:09 AM, mephysto wrote: Hi Adrian, it is not an artifact. This log comes from a multiplayer game, and this is an specific test to replicate the error. Practically, there are two users that execute the same operation, so you can see the simultaneous selects. My opinion was every session was isolated from others and temporary table was atomic for every session (transaction). Well sessions and transactions are not the same thing. A simple explanation: A session is a specific connection to a database. A transaction is a unit of work. It can be explicitly bounded by BEGIN/COMMIT(ROLLBACK). In Postgres if you do not supply the BEGIN each statement gets one, so each statement is in a transaction. So a session can have one transaction or multiple. But I think that I'm not true. Are The two selects in the same session in my case? Why? The issue to me at least, assuming Postgres is working properly, is that you have two selects in the same transaction. The ON COMMIT DROP should drop the table at the end of the transaction. Now as Alban wrote it is possible that the table is being cached somehow. One suggestion I on that line of thought is to use dynamic commands: http://www.postgresql.org/docs/9.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN It is also possible, as mentioned previously, that the Java code is misbehaving. That there are threads interfering with each other. I am not a Java programmer so I can not be of any help there. Thanks in advance. Mephysto -- Adrian Klaver adrian.kla...@gmail.com -- 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] Ordering Results by a Supplied Order
Ok, thanks ^^ Cheers, Rémi-C 2014-02-06 Vik Fearing vik.fear...@dalibo.com: On 02/06/2014 10:00 AM, Rémi Cura wrote: Hey, I dont understand the difference between this ORDINALITY option and adding a row_number() over() in the SELECT. WITH ORDINALITY will give you something to order by. You should never do row_number() over () because that will give you potentially random results. -- Vik
[GENERAL] Offending My Tender Sensibilities -OR- OLTP on a Star Schema
We have an OLTP database and no data warehouse. We are currently planning out a build for a data warehouse however (possibly using Hadoop). X is recommending that we convert our current, normalized OLTP database into a flattened Star Schema. The primary rationale for this OLTP flattening is that since most of the app calls to the database are SELECT statements, the DB should be optimized for that. A peripheral rationale I've heard is that the 1NF-3NF database is hard to understand sometimes. I'm not going to state what I believe. I would like to hear my esteemed colleagues take on this situation. What would you do? What would you say? Is there anything you would investigate? In short, what say you? Thanks for your time. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Help with connection issue - started today
Dear all, I need your advise. Found a tricky situation. Without any changes in the configuration files, a **local** connection to a local VPN IP address could not be established with the following error: 2014-02-05 09:12:21.111 - x - org.postgresql.util.PSQLException: ERROR: could not establish connection Detail: FATAL: no pg_hba.conf entry for host 10.68.73.6, user master, database master, SSL off org.postgresql.util.PSQLException: ERROR: could not establish connection Detail: FATAL: no pg_hba.conf entry for host 10.68.73.6, user master, database master, SSL off at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:331) at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228) at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228) at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228) at br.com... If I run from the Local Server: [root@mylocalserver logs]# psql -h 10.68.73.6 -d master -U master Senha para usuário master: psql (9.2.6) Digite help para ajuda. master= select count(*) from pessoa; count --- 9 (1 registro) If I run from a Remote Server: [root@myremoteserver ssh]# psql -h 10.68.73.6 -d master -U master Senha para usuário master: psql (9.2.6) Digite help para ajuda. master= select count(*) from pessoa; count --- 9 (1 registro) So, seems that only JDBC is getting in trouble to connect to a local IP address. Current interfaces (and yes, the tunneling is running - otherwise the remote server could not connect): [root@mylocalserver logs]# ifconfig eth0 Link encap:Ethernet Endereço de HW XX:XX:XX:XX:XX:XX inet end.: xxx.xx.xxx.85 Bcast:xxx.xx.xxx.87 Masc:255.255.255.248 endereço inet6: ::xxx:::/64 Escopo:Link UP BROADCASTRUNNING MULTICAST MTU:1500 Métrica:1 RX packets:172557 errors:0 dropped:0 overruns:0 frame:0 TX packets:293439 errors:0 dropped:0 overruns:0 carrier:0 colisões:0 txqueuelen:1000 RX bytes:24537681 (23.4 MiB) TX bytes:227413210 (216.8 MiB) IRQ:28 Memória:fb00-fb7f loLink encap:Loopback Local inet end.: 127.0.0.1 Masc:255.0.0.0 endereço inet6: ::1/128 Escopo:Máquina UP LOOPBACKRUNNING MTU:16436 Métrica:1 RX packets:138156 errors:0 dropped:0 overruns:0 frame:0 TX packets:138156 errors:0 dropped:0 overruns:0 carrier:0 colisões:0 txqueuelen:0 RX bytes:31993170 (30.5 MiB) TX bytes:31993170 (30.5 MiB) tun0 Link encap:Não Especificado Endereço de HW 00-00-00-00-00-00-00-00-00-00-00-00-00-00-00-00 inet end.: 10.68.73.6 P-a-P:10.68.73.5 Masc:255.255.255.255 UP POINTOPOINT RUNNING NOARP MULTICAST MTU:1500 Métrica:1 RX packets:149106 errors:0 dropped:0 overruns:0 frame:0 TX packets:274534 errors:0 dropped:199 overruns:0 carrier:0 colisões:0 txqueuelen:100 RX bytes:6291572 (6.0 MiB) TX bytes:381732404 (364.0 MiB) -- 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] Offending My Tender Sensibilities -OR- OLTP on a Star Schema
On Tue, Feb 4, 2014 at 10:06 PM, Roy Anderson roy.ander...@gmail.com wrote: We have an OLTP database and no data warehouse. We are currently planning out a build for a data warehouse however (possibly using Hadoop). X is recommending that we convert our current, normalized OLTP database into a flattened Star Schema. The primary rationale for this OLTP flattening is that since most of the app calls to the database are SELECT statements, the DB should be optimized for that. A peripheral rationale I've heard is that the 1NF-3NF database is hard to understand sometimes. I'm not going to state what I believe. I would like to hear my esteemed colleagues take on this situation. What would you do? What would you say? Is there anything you would investigate? In short, what say you? couple quick thoughts: *) what kinds of problems is the data warehouse going to solve the current database is not solving? *) Have you conclusively proven the current database can't solve those problems? *) the phrase '1NF-3NF is poor for very general application style X' is a typical comment from someone who read a book or an article on databases without having a lot of practical experience in implementing systems. Experienced database guys rarely talk in such terms; they slip in an out of 'normal forms' naturally in order to meet the demands of whatever task they may be happening to solve. *) a deficit in knowledge in scaling large data is typically made worse, not better, by switching up the software stack, particularly if you don't have very clear idea of what problems you are trying to solve by switching and what problems you will run into with the new stack *) Hardware is on the march (SSD). Large data scaling is often a function of hardware, not software. Scaling out to multiple systems from a monolithic architecture is not a decision to be taken lightly and the calculus of that decision must be constantly revisited in the face of hardware improvements. Scaling horizontally may ultimately cost more in hardware in the end. *) Anyone who has ever uttered the statement: databases are slower than technology X because databases utilize disks and technology X stores everything in memory has absolutely no concept of how databases (or operating systems) work. *) Star schemas are great for supporting certain kinds of OLAP tools but are IMNSHO overrated. If your reporting requirements are extremely generic or not very well defined you may have to use that or a similar pattern however. *) There is no rule that says if you use databases there must only be one. Partitioning databases for large analytical queries is a reasonable path if you have tons of data. You can then have the application or (my preference) dblink then aggregate data together. *) Do not consider any advice to implement exotic storage backend from someone that has not previously implemented that same technology on a similar scale on a previous project, ever. Data of large scale is hard. Installing magical tool X often besides not solving the problem gives you another difficult problem to solve. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgresql GROUP BY SIMILAR but not equal values
I wanted to ask you the following question to all experts here. Let's say I have this table foo ID|G1|T1| 1|2|ABC| 1|2|ABCD| 1|2|DEF| 1|2|DEFG| SELECT * FROM foo GROUP BY ID,G1,T1 RETURNS exactly the same table. Is there a way in SQL or PostgreSQL in general to group by values than are not exactly the same but are quite similar (like 'ABC' and 'ABCD') based on some distance function (levenshtein for example) if the distance is within some threshold (i.e., 1) My intuition is that SQL cannot support such queries but I was wondering if there was some hack around it. The problem as I see it that distance functions require 2 values but GROUP BY only checks equality. Another subproblem that might help is can we overload an operator of a custom type, so that equals operator is more relaxed and is calculated by a function? OR can we use GROUP BY with a custom comparator for a data type? I hope my question makes sense. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Postgresql-GROUP-BY-SIMILAR-but-not-equal-values-tp5790860.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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 connection issue - started today
On Wed, Feb 5, 2014 at 5:21 AM, Edson Richter edsonrich...@hotmail.com wrote: Dear all, I need your advise. Found a tricky situation. Without any changes in the configuration files, a **local** connection to a local VPN IP address could not be established with the following error: 2014-02-05 09:12:21.111 - x - org.postgresql.util.PSQLException: ERROR: could not establish connection Detail: FATAL: no pg_hba.conf entry for host 10.68.73.6, user master, database master, SSL off org.postgresql.util.PSQLException: ERROR: could not establish connection Detail: FATAL: no pg_hba.conf entry for host 10.68.73.6, user master, database master, SSL off at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:331) at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228) at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228) at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228) at br.com... If I run from the Local Server: [root@mylocalserver logs]# psql -h 10.68.73.6 -d master -U master Senha para usuário master: psql (9.2.6) Digite help para ajuda. master= select count(*) from pessoa; count --- 9 (1 registro) If I run from a Remote Server: [root@myremoteserver ssh]# psql -h 10.68.73.6 -d master -U master Senha para usuário master: psql (9.2.6) Digite help para ajuda. master= select count(*) from pessoa; count --- 9 (1 registro) So, seems that only JDBC is getting in trouble to connect to a local IP address. Current interfaces (and yes, the tunneling is running - otherwise the remote server could not connect): [root@mylocalserver logs]# ifconfig eth0 Link encap:Ethernet Endereço de HW XX:XX:XX:XX:XX:XX inet end.: xxx.xx.xxx.85 Bcast:xxx.xx.xxx.87 Masc:255.255.255.248 endereço inet6: ::xxx:::/64 Escopo:Link UP BROADCASTRUNNING MULTICAST MTU:1500 Métrica:1 RX packets:172557 errors:0 dropped:0 overruns:0 frame:0 TX packets:293439 errors:0 dropped:0 overruns:0 carrier:0 colisões:0 txqueuelen:1000 RX bytes:24537681 (23.4 MiB) TX bytes:227413210 (216.8 MiB) IRQ:28 Memória:fb00-fb7f loLink encap:Loopback Local inet end.: 127.0.0.1 Masc:255.0.0.0 endereço inet6: ::1/128 Escopo:Máquina UP LOOPBACKRUNNING MTU:16436 Métrica:1 RX packets:138156 errors:0 dropped:0 overruns:0 frame:0 TX packets:138156 errors:0 dropped:0 overruns:0 carrier:0 colisões:0 txqueuelen:0 RX bytes:31993170 (30.5 MiB) TX bytes:31993170 (30.5 MiB) tun0 Link encap:Não Especificado Endereço de HW 00-00-00-00-00-00-00-00-00-00-00-00-00-00-00-00 inet end.: 10.68.73.6 P-a-P:10.68.73.5 Masc:255.255.255.255 UP POINTOPOINT RUNNING NOARP MULTICAST MTU:1500 Métrica:1 RX packets:149106 errors:0 dropped:0 overruns:0 frame:0 TX packets:274534 errors:0 dropped:199 overruns:0 carrier:0 colisões:0 txqueuelen:100 RX bytes:6291572 (6.0 MiB) TX bytes:381732404 (364.0 MiB) The log is telling you exactly what's happening. pg_hba.conf is a database firewall of sorts that manages whom is allowed to connect to the database and from where. Fixing this is a matter of adding a rule to that file. The file is internally well documented but you should also read this: http://www.postgresql.org/docs/9.3/static/auth-pg-hba-conf.html after changing the file, you have to reload or restart the database. merlin -- 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 GROUP BY SIMILAR but not equal values
alexandros_e alexandros...@gmail.com writes: Is there a way in SQL or PostgreSQL in general to group by values than are not exactly the same but are quite similar (like 'ABC' and 'ABCD') based on some distance function (levenshtein for example) if the distance is within some threshold (i.e., 1) Well, you can GROUP BY the result of a function. You are going to have to think harder than the above in any case. For example, it's not hard to imagine a similarity operator that says that A is similar to B, and B is similar to C, but if you ask it to compare A to C it says they're not similar (enough). Now what? Are A,B,C all part of the same group? If you take the transitive closure of such an operator you probably end up with everything in one group; but if you don't, it's hard to see a principled result at all. If you can cast your problem as transformation of the values into some canonical or representative form, then you can do that and then group on simple equality of the canonical values. For instance case-insensitive grouping is customarily done with GROUP BY lower(x) regards, tom lane -- 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 connection issue - started today
On 02/06/2014 07:29 AM, Merlin Moncure wrote: The log is telling you exactly what's happening. pg_hba.conf is a database firewall of sorts that manages whom is allowed to connect to the database and from where. Fixing this is a matter of adding a rule to that file. The file is internally well documented but you should also read this: http://www.postgresql.org/docs/9.3/static/auth-pg-hba-conf.html after changing the file, you have to reload or restart the database. Actually I think this is the first try at what became this thread: http://www.postgresql.org/message-id/blu0-smtp4003f13d23f226d5cdee60acf...@phx.gbl It turned out to be a Tomcat issue. merlin -- Adrian Klaver adrian.kla...@gmail.com -- 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 GROUP BY SIMILAR but not equal values
On 6 February 2014 16:18, alexandros_e alexandros...@gmail.com wrote: Let's say I have this table foo ID|G1|T1| 1|2|ABC| 1|2|ABCD| 1|2|DEF| 1|2|DEFG| SELECT * FROM foo GROUP BY ID,G1,T1 Is there a way in SQL or PostgreSQL in general to group by values than are not exactly the same but are quite similar (like 'ABC' and 'ABCD') based on some distance function (levenshtein for example) if the distance is within some threshold (i.e., 1) Perhaps there is: You can calculate the levenshtein distance between those values using a self-join and then GROUP BY the result of that expression and limit the results with HAVING. For example: SELECT foo1.ID, foo1.G1, foo1.T1, levenshtein(foo1.T1, foo2.T1) FROM foo foo1 INNER JOIN foo foo2 ON (foo2.ID = foo1.ID AND foo2.G1 = foo1.G1) GROUP BY foo1.ID, foo1.G1, foo1.T1, levenshtein(foo1.T1, foo2.T1) HAVING levenshtein(foo1.T1, foo2.T1) 1 Is that what you're looking for? -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest. -- 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 GROUP BY SIMILAR but not equal values
On Thu, Feb 6, 2014 at 7:41 AM, Alban Hertroys haram...@gmail.com wrote: On 6 February 2014 16:18, alexandros_e alexandros...@gmail.com wrote: Is there a way in SQL or PostgreSQL in general to group by values than are not exactly the same but are quite similar (like 'ABC' and 'ABCD') based on some distance function (levenshtein for example) if the distance is within some threshold (i.e., 1) Perhaps there is: You can calculate the levenshtein distance between those values using a self-join and then GROUP BY the result of that expression and limit the results with HAVING. For example: SELECT foo1.ID, foo1.G1, foo1.T1, levenshtein(foo1.T1, foo2.T1) FROM foo foo1 INNER JOIN foo foo2 ON (foo2.ID = foo1.ID AND foo2.G1 = foo1.G1) GROUP BY foo1.ID, foo1.G1, foo1.T1, levenshtein(foo1.T1, foo2.T1) HAVING levenshtein(foo1.T1, foo2.T1) 1 From my understanding of the question, probably, adding another levenshtein distance to some base value will make more sense: GROUP BY levenshtein(foo1.T1, foo2.T1), levenshtein(foo1.T1, 'A') Though, it looks like a clusterization task for me, and therefore I would recommend OP to look at the PL/R http://www.joeconway.com/plr/doc/index.html. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.com -- 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 GROUP BY SIMILAR but not equal values
What about a regexp match ? -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Tom Lane Sent: Thursday, February 06, 2014 10:32 AM To: alexandros_e Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Postgresql GROUP BY SIMILAR but not equal values alexandros_e alexandros...@gmail.com writes: Is there a way in SQL or PostgreSQL in general to group by values than are not exactly the same but are quite similar (like 'ABC' and 'ABCD') based on some distance function (levenshtein for example) if the distance is within some threshold (i.e., 1) Well, you can GROUP BY the result of a function. You are going to have to think harder than the above in any case. For example, it's not hard to imagine a similarity operator that says that A is similar to B, and B is similar to C, but if you ask it to compare A to C it says they're not similar (enough). Now what? Are A,B,C all part of the same group? If you take the transitive closure of such an operator you probably end up with everything in one group; but if you don't, it's hard to see a principled result at all. If you can cast your problem as transformation of the values into some canonical or representative form, then you can do that and then group on simple equality of the canonical values. For instance case-insensitive grouping is customarily done with GROUP BY lower(x) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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 connection issue - started today
You checked pg_hba_conf for host 10.68.73.6? Is there a matching log entry for the rejection? On Wed, 2014-02-05 at 09:21 -0200, Edson Richter wrote: Dear all, I need your advise. Found a tricky situation. Without any changes in the configuration files, a **local** connection to a local VPN IP address could not be established with the following error: 2014-02-05 09:12:21.111 - x - org.postgresql.util.PSQLException: ERROR: could not establish connection Detail: FATAL: no pg_hba.conf entry for host 10.68.73.6, user master, database master, SSL off org.postgresql.util.PSQLException: ERROR: could not establish connection Detail: FATAL: no pg_hba.conf entry for host 10.68.73.6, user master, database master, SSL off at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:331) at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228) at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228) at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228) at br.com... If I run from the Local Server: [root@mylocalserver logs]# psql -h 10.68.73.6 -d master -U master Senha para usuário master: psql (9.2.6) Digite help para ajuda. master= select count(*) from pessoa; count --- 9 (1 registro) If I run from a Remote Server: [root@myremoteserver ssh]# psql -h 10.68.73.6 -d master -U master Senha para usuário master: psql (9.2.6) Digite help para ajuda. master= select count(*) from pessoa; count --- 9 (1 registro) So, seems that only JDBC is getting in trouble to connect to a local IP address. Current interfaces (and yes, the tunneling is running - otherwise the remote server could not connect): [root@mylocalserver logs]# ifconfig eth0 Link encap:Ethernet Endereço de HW XX:XX:XX:XX:XX:XX inet end.: xxx.xx.xxx.85 Bcast:xxx.xx.xxx.87 Masc:255.255.255.248 endereço inet6: ::xxx:::/64 Escopo:Link UP BROADCASTRUNNING MULTICAST MTU:1500 Métrica:1 RX packets:172557 errors:0 dropped:0 overruns:0 frame:0 TX packets:293439 errors:0 dropped:0 overruns:0 carrier:0 colisões:0 txqueuelen:1000 RX bytes:24537681 (23.4 MiB) TX bytes:227413210 (216.8 MiB) IRQ:28 Memória:fb00-fb7f loLink encap:Loopback Local inet end.: 127.0.0.1 Masc:255.0.0.0 endereço inet6: ::1/128 Escopo:Máquina UP LOOPBACKRUNNING MTU:16436 Métrica:1 RX packets:138156 errors:0 dropped:0 overruns:0 frame:0 TX packets:138156 errors:0 dropped:0 overruns:0 carrier:0 colisões:0 txqueuelen:0 RX bytes:31993170 (30.5 MiB) TX bytes:31993170 (30.5 MiB) tun0 Link encap:Não Especificado Endereço de HW 00-00-00-00-00-00-00-00-00-00-00-00-00-00-00-00 inet end.: 10.68.73.6 P-a-P:10.68.73.5 Masc:255.255.255.255 UP POINTOPOINT RUNNING NOARP MULTICAST MTU:1500 Métrica:1 RX packets:149106 errors:0 dropped:0 overruns:0 frame:0 TX packets:274534 errors:0 dropped:199 overruns:0 carrier:0 colisões:0 txqueuelen:100 RX bytes:6291572 (6.0 MiB) TX bytes:381732404 (364.0 MiB) -- 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] Ordering Results by a Supplied Order
On Wed, Feb 5, 2014 at 7:16 PM, Michael Sacket msac...@gammastream.com wrote: Often times I find it necessary to work with table rows in a specific, generally user-supplied order. It could be anything really that requires an ordering that can't come from a natural column. Most of the time this involved manipulating a position column from the client application. In any case, I've often found that to be cumbersome, but I think I've come up with a solution that some of you may find useful. There also are some ways of doing it without creating an additional column http://www.depesz.com/2010/07/25/how-to-order-by-some-random-query-defined-values/. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.com -- 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 GROUP BY SIMILAR but not equal values
Conceptually, Tom (as always) is right. But Alban's hack help. DROP TABLE foo; CREATE TABLE IF NOT EXISTS foo(ID INTEGER,G1 INTEGER, T1 TEXT, ID2 SERIAL PRIMARY KEY); INSERT INTO foo(ID,G1,T1) VALUES(1,2,'ABC'); INSERT INTO foo(ID,G1,T1) VALUES(1,2,'ABCD'); INSERT INTO foo(ID,G1,T1) VALUES(1,2,'ABDC'); INSERT INTO foo(ID,G1,T1) VALUES(1,2,'DEF'); INSERT INTO foo(ID,G1,T1) VALUES(1,2,'DEFH'); /* A little editing to remove duplicates a to b and b to a */ SELECT foo1.ID, foo1.G1, foo1.T1, levenshtein(foo1.T1, foo2.T1),foo2.T1 FROM foo foo1 INNER JOIN foo foo2 ON (foo2.ID = foo1.ID AND foo2.G1 = foo1.G1) WHERE foo1.ID2foo2.ID2 GROUP BY foo1.ID, foo1.G1, foo1.T1, levenshtein(foo1.T1, foo2.T1),foo2.T1 HAVING levenshtein(foo1.T1, foo2.T1) 2; RETURNS ID|G1|foo1.T1|foo2.T1 1;2;ABC;1;ABCD 1;2;ABC;1;ABDC 1;2;ABCD;2;ABDC 1;2;DEF;1;DEFH Then it requires a second grouping but as Tom suggested it would be hard to somehow group all similar cases together because then it becomes a clustering problem. With a second grouping we will have 3 records instead of 4, so it is better than the initial case by 25%. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Postgresql-GROUP-BY-SIMILAR-but-not-equal-values-tp5790860p5790876.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] Ordering Results by a Supplied Order
On Feb 6, 2014, at 2:23 AM, Vik Fearing wrote: On 02/06/2014 04:16 AM, Michael Sacket wrote: Often times I find it necessary to work with table rows in a specific, generally user-supplied order. It could be anything really that requires an ordering that can't come from a natural column. Most of the time this involved manipulating a position column from the client application. In any case, I've often found that to be cumbersome, but I think I've come up with a solution that some of you may find useful. Up until 9.4, that's a good way to do it. Starting from 9.4, you can use the WITH ORDINALITY feature. http://www.postgresql.org/docs/devel/static/sql-select.html -- Vik Even better! The development team is always making my work easier in unexpected ways. Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] subtracting from a date
I have reviewed working with dates documentation but some things are not clear and I cannot get an example to work for what I need. I am passing a date into a stored function like '2013-04-01' The stored function accepts this string as a date type. Within the function, I need to: 1. subtract one year from this date into another date type 2. subtract one month from this date into another date type 3. subtract one day from this date into another date type Are there any examples of this? This is not a timestamp type, but a date type. thanks
Re: [GENERAL] subtracting from a date
On 02/06/2014 09:25 AM, Jay Vee wrote: I have reviewed working with dates documentation but some things are not clear and I cannot get an example to work for what I need. I am passing a date into a stored function like '2013-04-01' The stored function accepts this string as a date type. Within the function, I need to: 1. subtract one year from this date into another date type 2. subtract one month from this date into another date type 3. subtract one day from this date into another date type Are there any examples of this? This is not a timestamp type, but a date type. Something like this: test= select '2013-04-01'::date - interval '1 yr'; ?column? - 2012-04-01 00:00:00 (1 row) test= select '2013-04-01'::date - interval '1 month'; ?column? - 2013-03-01 00:00:00 (1 row) test= select '2013-04-01'::date - interval '1 day'; ?column? - 2013-03-31 00:00:00 You did say what language you are using for the function so the assignment will depend on that. thanks -- Adrian Klaver adrian.kla...@gmail.com -- 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] subtracting from a date
On 02/06/2014 09:25 AM, Jay Vee wrote: I have reviewed working with dates documentation but some things are not clear and I cannot get an example to work for what I need. I am passing a date into a stored function like '2013-04-01' The stored function accepts this string as a date type. Within the function, I need to: 1. subtract one year from this date into another date type 2. subtract one month from this date into another date type 3. subtract one day from this date into another date type Are there any examples of this? This is not a timestamp type, but a date type. thanks The basics are easy. Cast the string to a date and subtract the appropriate interval: '2013-04-01'::date - '1 year'::interval BUT... PostgreSQL, like all programs, makes certain assumptions about dates and intervals. Generally they are good but you need to be sure they match your requirements lest you be surprised. For example: 1 day may or may not be 24 hours (DST changeovers) 1 month has varying numbers of days Same thing with 1 year (leap years) Certain calculations will give different results depending on what timezone you set due to the different scheduling and handling of summer/winter time offsets. Operator precedence is important. You might expect select '2012-02-29'::date - '1 year'::interval + '1 year'::interval; to return 2012-02-29 but it will return 2012-02-28 since February 2011 has no 29th and 2011-02-28 plus a year is 2012-02-28. For an amusing take on date/time calculations see: http://www.youtube.com/watch?v=-5wpm-gesOY Cheers, Steve -- 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] subtracting from a date
I tried that but get an error v_start_date date; v_minus_one_year date; I have v_start_date to start with and want to subtract one year and put into v_minus_one_year select v_start_date - interval '1 yr' as v_minus_one_year; --- I get: ERROR: query has no destination for result data On Thu, Feb 6, 2014 at 10:31 AM, Adrian Klaver adrian.kla...@gmail.comwrote: On 02/06/2014 09:25 AM, Jay Vee wrote: I have reviewed working with dates documentation but some things are not clear and I cannot get an example to work for what I need. I am passing a date into a stored function like '2013-04-01' The stored function accepts this string as a date type. Within the function, I need to: 1. subtract one year from this date into another date type 2. subtract one month from this date into another date type 3. subtract one day from this date into another date type Are there any examples of this? This is not a timestamp type, but a date type. Something like this: test= select '2013-04-01'::date - interval '1 yr'; ?column? - 2012-04-01 00:00:00 (1 row) test= select '2013-04-01'::date - interval '1 month'; ?column? - 2013-03-01 00:00:00 (1 row) test= select '2013-04-01'::date - interval '1 day'; ?column? - 2013-03-31 00:00:00 You did say what language you are using for the function so the assignment will depend on that. thanks -- Adrian Klaver adrian.kla...@gmail.com
Re: [GENERAL] subtracting from a date
Jay Vee jvsr...@gmail.com writes: I tried that but get an error ? ? v_start_date date; ? ? v_minus_one_year date; I have v_start_date to start with and want to subtract one year and put into v_minus_one_year ?? ? select v_start_date - interval '1 yr' as v_minus_one_year; sj$ cat q create or replace function foo(date) returns date as $$ declare foo date; begin select into foo $1 - '1 year'::interval; return foo; end $$ language plpgsql; select foo(current_date); sj$ psql -f q SET CREATE FUNCTION foo 2013-02-06 (1 row) HTH --- ?I get: ERROR: ?query has no destination for result data On Thu, Feb 6, 2014 at 10:31 AM, Adrian Klaver adrian.kla...@gmail.com wrote: On 02/06/2014 09:25 AM, Jay Vee wrote: I have reviewed working with dates documentation but some things are not clear and I cannot get an example to work for what I need. I am passing a date into a stored function like '2013-04-01' The stored function accepts this string as a date type. Within the function, I need to: 1. ?subtract one year from this date into another date type 2. ?subtract one month from this date into another date type 3. ?subtract one day from this date into another date type Are there any examples of this? ?This is not a timestamp type, but a date type. Something like this: test= select '2013-04-01'::date - interval '1 yr'; ? ? ? ?column? - ?2012-04-01 00:00:00 (1 row) test= select '2013-04-01'::date - interval '1 month'; ? ? ? ?column? - ?2013-03-01 00:00:00 (1 row) test= select '2013-04-01'::date - interval '1 day'; ? ? ? ?column? - ?2013-03-31 00:00:00 You did say what language you are using for the function so the assignment will depend on that. thanks -- Adrian Klaver adrian.kla...@gmail.com -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- 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] Offending My Tender Sensibilities -OR- OLTP on a Star Schema
On Thu, Feb 6, 2014 at 7:51 AM, Merlin Moncure mmonc...@gmail.com wrote: *) Do not consider any advice to implement exotic storage backend from someone that has not previously implemented that same technology on a similar scale on a previous project, ever. Data of large scale is hard. Installing magical tool X often besides not solving the problem gives you another difficult problem to solve. Merlin, this reminds me of the quote from Mencken: For every complex problem there is an answer that is clear, simple, and wrong. -- 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] Ordering Results by a Supplied Order
On 07/02/14 05:43, Michael Sacket wrote: On Feb 6, 2014, at 2:23 AM, Vik Fearing wrote: On 02/06/2014 04:16 AM, Michael Sacket wrote: Often times I find it necessary to work with table rows in a specific, generally user-supplied order. It could be anything really that requires an ordering that can't come from a natural column. Most of the time this involved manipulating a position column from the client application. In any case, I've often found that to be cumbersome, but I think I've come up with a solution that some of you may find useful. Up until 9.4, that's a good way to do it. Starting from 9.4, you can use the WITH ORDINALITY feature. http://www.postgresql.org/docs/devel/static/sql-select.html -- Vik Even better! The development team is always making my work easier in unexpected ways. Thanks! You do realize, that with this new feature, the licence fee for PostgreSQL will dramatically increase? :-) Cheers, Gavin -- 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] Ordering Results by a Supplied Order
On 02/06/2014 11:57 AM, Gavin Flower wrote: On 07/02/14 05:43, Michael Sacket wrote: On Feb 6, 2014, at 2:23 AM, Vik Fearing wrote: On 02/06/2014 04:16 AM, Michael Sacket wrote: Often times I find it necessary to work with table rows in a specific, generally user-supplied order. It could be anything really that requires an ordering that can't come from a natural column. Most of the time this involved manipulating a position column from the client application. In any case, I've often found that to be cumbersome, but I think I've come up with a solution that some of you may find useful. Up until 9.4, that's a good way to do it. Starting from 9.4, you can use the WITH ORDINALITY feature. http://www.postgresql.org/docs/devel/static/sql-select.html -- Vik Even better! The development team is always making my work easier in unexpected ways. Thanks! You do realize, that with this new feature, the licence fee for PostgreSQL will dramatically increase? :-) Cheers, Gavin A ten-fold increase at least!
[GENERAL] NOT DEFERRABLE vs. DEFERRABLE INITIALLY IMMEDIATE constraints
I've been plagued several times by NOT DEFERRABLE constraints. Is there any good reason to define a constraint as NOT DEFERRABLE rather than DEFERRABLE INITIALLY IMMEDIATE? For example, is there performance penalty for PostgreSQL being prepared to defer a constraint even though it is not currently being deferred? The only downside I see to DEFERRABLE INITIALLY IMMEDIATE is that a naive user could needless set it to deferred, and thus use more memory/time than they otherwise would. But there are so many ways for naive users to shoot themselves in the foot, I fail to see the point in foreclosing this one possibility. Cheers, Jeff
Re: [GENERAL] NOT DEFERRABLE vs. DEFERRABLE INITIALLY IMMEDIATE constraints
Jeff Janes jeff.ja...@gmail.com writes: I've been plagued several times by NOT DEFERRABLE constraints. Is there any good reason to define a constraint as NOT DEFERRABLE rather than DEFERRABLE INITIALLY IMMEDIATE? For example, is there performance penalty for PostgreSQL being prepared to defer a constraint even though it is not currently being deferred? There's a substantial performance difference between deferrable and nondeferrable uniqueness constraints (ie, indexes). For foreign keys I don't believe it matters. We don't implement deferrability for other types of constraints such as CHECK. regards, tom lane -- 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] Offending My Tender Sensibilities -OR- OLTP on a Star Schema
On Thu, Feb 6, 2014 at 12:44 PM, Scott Marlowe scott.marl...@gmail.com wrote: On Thu, Feb 6, 2014 at 7:51 AM, Merlin Moncure mmonc...@gmail.com wrote: *) Do not consider any advice to implement exotic storage backend from someone that has not previously implemented that same technology on a similar scale on a previous project, ever. Data of large scale is hard. Installing magical tool X often besides not solving the problem gives you another difficult problem to solve. Merlin, this reminds me of the quote from Mencken: For every complex problem there is an answer that is clear, simple, and wrong. well said, heh. merlin -- 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] Offending My Tender Sensibilities -OR- OLTP on a Star Schema
On Feb 6, 2014, at 12:44 PM, Scott Marlowe scott.marl...@gmail.com wrote: Merlin, this reminds me of the quote from Mencken: For every complex problem there is an answer that is clear, simple, and wrong. Or as Niklaus Wirth said. ... complexity has and will maintain a strong fascination for many people. It is true that we live in a complex world and strive to solve inherently complex problems, which often do require complex mechanisms. However, this should not diminish our desire for elegant solutions, which convince by their clarity and effectiveness. Simple, elegant solutions are more effective, but they are harder to find than complex ones, and they require more time, which we to often believe to be unaffordable. Communications of the ACM, Feb. 1985 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Exclude pg_log directory when running pg_basebackup?
I found there is no option to disable sync for pg_log in pg_basebackup, maybe there should be one option to disalbe it?
Re: [GENERAL] subtracting from a date
jvsrvcs wrote I tried that but get an error v_start_date date; v_minus_one_year date; I have v_start_date to start with and want to subtract one year and put into v_minus_one_year select v_start_date - interval '1 yr' as v_minus_one_year; --- I get: ERROR: query has no destination for result data The relevant section of the documentation you need to study is: http://www.postgresql.org/docs/9.3/interactive/plpgsql-statements.html There are two basic ways to assign to variables: variable := expression SELECT expression[s] INTO variable[s] FROM ... || SELECT expression[s] FROM ... INTO variable[s] Your problem is that: SELECT expression AS variable FROM ... simply provides an alias for the expression and has nothing to do with any variables in the surrounding program. The INTO keyword is needed to avoid ambiguity. pl/pgsql has the unique behavior that using SELECT without INTO results in an error. If you really need to execute a SELECT and ignore the content selected you have to use PERFORM. The error you saw was this behavior in action. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/subtracting-from-a-date-tp5790891p5790923.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] Offending My Tender Sensibilities -OR- OLTP on a Star Schema
On 2/4/2014 10:06 PM, Roy Anderson wrote: We have an OLTP database and no data warehouse. We are currently planning out a build for a data warehouse however (possibly using Hadoop). X is recommending that we convert our current, normalized OLTP database into a flattened Star Schema. The primary rationale for this OLTP flattening is that since most of the app calls to the database are SELECT statements, the DB should be optimized for that. A peripheral rationale I've heard is that the 1NF-3NF database is hard to understand sometimes. I'm not going to state what I believe. I would like to hear my esteemed colleagues take on this situation. What would you do? What would you say? Is there anything you would investigate? In short, what say you? Thanks for your time. I think NF is more about correctness than about speed. People saying they denormalized a database to improve speed also de-corrected it, and I doubt they improved the speed. The only experience I have with data warehousing is storing my apache logs in PG. I have have many mega-millions of rows. So many that I don't run select count(*) because it takes forever. I have an index on hitdate, so I can pull any small number of records based on time very quickly, but I can never select all of them. When I add detail rows to the table I also add to a table of hourly summaries. My graphs are run off the summary tables and pulling a few months worth of data is very quick. So I guess it would come down to how you need to access your data warehouse. If you need to learn things by hitting every detail record for all time, then PG isn't gonna work well for you. If you can do indexed queries and return small subsets, PG will work great. If you can keep ongoing summaries you'll be fine too. Rebuilding a summary table (or creating a new one) is painful, but possible as long as your not in a huge hurry. -Andy -- 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 connection issue - started today
Em 06/02/2014 14:25, Bret Stern escreveu: You checked pg_hba_conf for host 10.68.73.6? Is there a matching log entry for the rejection? Thanks for your effort, Bret! Problem already solved (I've posted the solution in the list past days). Problem was a context.xml with a wrong database server reference - the reason that made Tomcat to start considering that file is a mystery to me, but removing the file, it became normal again. Regards, Edson On Wed, 2014-02-05 at 09:21 -0200, Edson Richter wrote: Dear all, I need your advise. Found a tricky situation. Without any changes in the configuration files, a **local** connection to a local VPN IP address could not be established with the following error: 2014-02-05 09:12:21.111 - x - org.postgresql.util.PSQLException: ERROR: could not establish connection Detail: FATAL: no pg_hba.conf entry for host 10.68.73.6, user master, database master, SSL off org.postgresql.util.PSQLException: ERROR: could not establish connection Detail: FATAL: no pg_hba.conf entry for host 10.68.73.6, user master, database master, SSL off at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:331) at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228) at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228) at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228) at br.com... If I run from the Local Server: [root@mylocalserver logs]# psql -h 10.68.73.6 -d master -U master Senha para usuário master: psql (9.2.6) Digite help para ajuda. master= select count(*) from pessoa; count --- 9 (1 registro) If I run from a Remote Server: [root@myremoteserver ssh]# psql -h 10.68.73.6 -d master -U master Senha para usuário master: psql (9.2.6) Digite help para ajuda. master= select count(*) from pessoa; count --- 9 (1 registro) So, seems that only JDBC is getting in trouble to connect to a local IP address. Current interfaces (and yes, the tunneling is running - otherwise the remote server could not connect): [root@mylocalserver logs]# ifconfig eth0 Link encap:Ethernet Endereço de HW XX:XX:XX:XX:XX:XX inet end.: xxx.xx.xxx.85 Bcast:xxx.xx.xxx.87 Masc:255.255.255.248 endereço inet6: ::xxx:::/64 Escopo:Link UP BROADCASTRUNNING MULTICAST MTU:1500 Métrica:1 RX packets:172557 errors:0 dropped:0 overruns:0 frame:0 TX packets:293439 errors:0 dropped:0 overruns:0 carrier:0 colisões:0 txqueuelen:1000 RX bytes:24537681 (23.4 MiB) TX bytes:227413210 (216.8 MiB) IRQ:28 Memória:fb00-fb7f loLink encap:Loopback Local inet end.: 127.0.0.1 Masc:255.0.0.0 endereço inet6: ::1/128 Escopo:Máquina UP LOOPBACKRUNNING MTU:16436 Métrica:1 RX packets:138156 errors:0 dropped:0 overruns:0 frame:0 TX packets:138156 errors:0 dropped:0 overruns:0 carrier:0 colisões:0 txqueuelen:0 RX bytes:31993170 (30.5 MiB) TX bytes:31993170 (30.5 MiB) tun0 Link encap:Não Especificado Endereço de HW 00-00-00-00-00-00-00-00-00-00-00-00-00-00-00-00 inet end.: 10.68.73.6 P-a-P:10.68.73.5 Masc:255.255.255.255 UP POINTOPOINT RUNNING NOARP MULTICAST MTU:1500 Métrica:1 RX packets:149106 errors:0 dropped:0 overruns:0 frame:0 TX packets:274534 errors:0 dropped:199 overruns:0 carrier:0 colisões:0 txqueuelen:100 RX bytes:6291572 (6.0 MiB) TX bytes:381732404 (364.0 MiB) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] client encoding that psql command sets
Not sure that it makes a difference but the docs say psql looks at LC_CTYPE not LANG for Unix systems. You did not say what OS you are working on though from the examples I am guessing some form of Unix. Thank you for the response. Sorry, I had not indicated OS information. OS information is the following: Red Hat Enterprise Linux Server release 6.2 Kernel 2.6.32-220.el6.x86_64 on an x86_64 The reason which was being verified using the LANG environment variable, I thought that the value of a LANG environment variable was set as LC_CTYPE when the LC_CTYPE environment variable and the LC_ALL environment variable are not set up. The LC_CTYPE environment variable was set up and re-verified. The result of psql command is following. [Result] % setenv LC_CTYPE ja_JP.eucJP % psql postgres -f test.txt -o result.txt EUC_JP % psql postgres -f test.txt result.txt UTF8 % psql postgres -o result.txt test.txt UTF8 % psql postgres test.txt result.txt UTF8 Even when a LC_CTYPE environment variable was set up, the result did not change. What do you think? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] password-less access, without using pg_hba
I'd like to provide public access, without a password, to a database hosted on Amazon RDS. I'm familiar with using pg_hba.conf to enable trust (no) authentication for a user. pg_hba.conf is not available to DBAs on RDS. Is there any other way to achieve password-less login in postgresql? I tried alter user password NULL. Thanks, Reece
Re: [GENERAL] password-less access, without using pg_hba
Reece Hart wrote I'd like to provide public access, without a password, to a database hosted on Amazon RDS. I'm familiar with using pg_hba.conf to enable trust (no) authentication for a user. pg_hba.conf is not available to DBAs on RDS. Is there any other way to achieve password-less login in postgresql? I tried alter user password NULL. Thanks, Reece Doubtful. You need to give people the correct server ip and user anyway so why not just give them a password at the same time? If you are trying to do some automated scripting there are other, better, solutions than disabling the password requirement. Especially on a public-visible server. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/password-less-access-without-using-pg-hba-tp5790947p5790948.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] password-less access, without using pg_hba
On 2/6/2014 6:07 PM, Reece Hart wrote: I'd like to provide public access, without a password, to a database hosted on Amazon RDS. I'm familiar with using pg_hba.conf to enable trust (no) authentication for a user. pg_hba.conf is not available to DBAs on RDS. Is there any other way to achieve password-less login in postgresql? I tried alter user password NULL. .pgpass is supported by any libpq based client. -- john r pierce 37N 122W somewhere on the middle of the left coast -- 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] password-less access, without using pg_hba
On Thu, Feb 6, 2014 at 6:37 PM, David Johnston pol...@yahoo.com wrote: Doubtful. Yeah, that's what I had assumed too. The question is motivated entirely by what I think would make it easier for users. In principle it's not difficult to give people a password (as I do now), but in practice it's a barrier that I'd like to eliminate. -Reece
[GENERAL] WAL archive on slave
Hello, My understanding is that WAL archiving can not be enabled on the slave in a streaming replication pair. If this is correct, is there a reason behind it? I can see logs showing up in pg_xlog, so could they not be archived? The reason I ask is if this happened it would allow the following with a streaming replication pair (A,B): 1. Start A as master 2. Attach B as slave using basebackup 3. work 4. Promote B to master 5. Restore A from a scheduled backup to a time before promotion 6. Attach A as slave pointing at B's WAL archive If we used A's WAL archive in this case and A had writes after the promotion then we would get timeline errors. As far as I can tell, using the WAL archive from B would resolve this issue. Or have I missed something here? Cheers, Cheers, James Sewell, PostgreSQL Team Lead / Solutions Architect __ Level 2, 50 Queen St, Melbourne VIC 3000 *P *(+61) 3 8370 8000 *W* www.lisasoft.com *F *(+61) 3 8370 8099 -- -- The contents of this email are confidential and may be subject to legal or professional privilege and copyright. No representation is made that this email is free of viruses or other defects. If you have received this communication in error, you may not copy or distribute any part of it or otherwise disclose its contents to anyone. Please advise the sender of your incorrect receipt of this correspondence.
Re: [GENERAL] password-less access, without using pg_hba
Reece Hart wrote On Thu, Feb 6, 2014 at 6:37 PM, David Johnston lt; polobo@ gt; wrote: Doubtful. Yeah, that's what I had assumed too. The question is motivated entirely by what I think would make it easier for users. In principle it's not difficult to give people a password (as I do now), but in practice it's a barrier that I'd like to eliminate. -Reece If your users are connecting directly to a PostgreSQL database then the presence or absence of a password has no significant impact on usability. They have learned SQL and can interact with databases and likely expect to need a password anyway. Usually developers make things easier by writing software that the users interact with instead of the database... David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/password-less-access-without-using-pg-hba-tp5790947p5790966.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] password-less access, without using pg_hba
On Thu, Feb 6, 2014 at 6:37 PM, David Johnston pol...@yahoo.com wrote: Doubtful. Yeah, that's what I had assumed too. The question is motivated entirely by what I think would make it easier for users. In principle it's not difficult to give people a password (as I do now), but in practice it's a barrier that I'd like to eliminate. +1. I told Amazon's RDS guy in Japan that it is a major pain for PostgreSQL users to not be able to touch pg_hba.conf. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
