Re: [GENERAL] ID column naming convention
On Tue, Oct 13, 2015 at 03:44:15PM -0700, John R Pierce wrote: > On 10/13/2015 3:27 PM, droberts wrote: > >Thanks. My only question is how do you create a schema diagram (ERD) then? > >The tool won't know what the relationships are unless maybe you put foreign > >key constraints on. BTW does anyone recommend a tool to to that? I've been > >playing with DbVisualizer. > > > I don't know of any ERD tool that will recognize foreign key references > without FK constraint definitions, regardless of what things are named. Also, there wouldn't be anything to recognize. Only to assume. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- 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] Understanding "seq scans"
On Tue, Oct 13, 2015 at 11:54 AM, Alvaro Herrera wrote: > Lele Gaifax wrote: >> Alvaro Herrera writes: >> >> > So 10% of your rows in the master_l10n table start with "quattro"? >> > That's pretty odd, isn't it? How did you manufacture these data? >> >> Well, not a real scenario for sure, but definitely not odd: I just needed an >> "extremely" big dataset to test out several different strategies, both on >> table layout and indexes. The tables are populated by mechanically >> translating >> the integer primary key into the corresponding "in words" string (1 -> >> "one")... > > I imagined it would be something like that. It's not the most useful > set of test data, precisely because it doesn't accurately reflect what > you're going to have in practice. I suggest you enter some actual text, > even if it's just text from Don Camillo or whatever. > >> > How often are you going to look for translated text without specifying a >> > language? >> >> Never. The most frequently used criteria is «LIKE '%word%'» in the context of >> a user session, and thus with a "preferred language". > > Be very careful with a % at the left. The index is not going to work at > all there. It is not the same as looking for stuff without a % at the > left. Note, you can optimize LIKE '%foo%' with pg_trgm/gin indexing. Trigram based indexing is kind of a mixed bag but is about to get a lot faster with recent enhancements so that it should mostly match or beat the brute force search. This is the preferred solution if you need to do partial string matching -- for most other cases of attribute searching I'd be looking at jsonb. Welcome to postgres OP! 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] problems with copy from file
hello @ll, i have a simpe table : test=# create table httpd_log(id serial primary key, data text); CREATE TABLE and i'm trying to import from a httpd-log, that contains this: kretschmer@tux:~$ cat test.log ... domain.de aaa.63.xx.yy - - [06/Nov/2014:00:48:22 +0100] "GET /index.php/impressum2/year.listevents/2015/01/08/101 HTTP/1.0" 200 28076 "-" "Mozilla/5.0 (compatible; MJ12bot/v1.4.5; http://www.majestic12.co.uk/bot.php?+)" "-" other-domain.de bb.243.xx.yyy - - [06/Nov/2014:00:48:22 +0100] "\x16\x03\x01\x01\xb1\x01" 501 1037 "-" "-" "-" domain.tld cc.249.xx.yy - - [06/Nov/2014:00:48:22 +0100] "GET /leipzig/transport?start=Uranusstr.&ziel=Finkengrund HTTP/1.1" 200 5610 "-" "Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)" "-" ... Url's and IP's changed by me. As you can see: simple ASCII-Text test=*# \copy httpd_log (data) from '~/test.log'; ERROR: invalid byte sequence for encoding "UTF8": 0xb1 CONTEXT: COPY httpd_log, line 3: "other-domain bb.243.xx.yyy - - [06/Nov/2014:00:48:22 +0100] "\x16\x03\x01\x01\xb1\x01" 501 10..." I have a solution: test=*# \copy apache_log (data) from program 'sed -e "s/\\x/x/g" test.log'; COPY 5 The table contains the corrent content: ... other-domain.de bb.243.xx.yyy - - [06/Nov/2014:00:48:22 +0100] "\x16\x03\x01\x01\xb1\x01" 501 1037 "-" "-" "-" ... But is there a way to COPY the file without external tools, in this case sed? Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] postgres function
Hi All, Do we have function like regexp_substr in postgres..? in oracle this function seach the - from 1 to 2 and return result, regexp_substr(PART_CATG_DESC,'[^-]+', 1, 2)
Re: [GENERAL] using postgresql for session
On Oct 7, 2015, at 11:58 AM, john.tiger wrote: > has anyone used postgres jsonb for holding session ? Since server side > session is really just a piece of data, why bother with special "session" > plugins and just use postgres to hold the data and retrieve it with psycopg2 > ? Maybe use some trigger if session changes?We are using python Bottle > with psycopg2 (super simple, powerful combo) - are we missing something > magical about session plugins ? I previously used TEXT or BLOB for holding session data, and pickled the data. I can't remember. If you're going to use PostgresSQL for the session, the big performance tip is to use partial index on the session key (assuming it's an md5-like hash). So you'd want a table that is something like this: CREATE TABLE session( session_id VARCHAR(32) PRIMARY KEY, session_data TEXT ); CREATE INDEX idx_session_partial ON session(substr(session_id , 0,5)) Then query like this SELECT * FROM session WHERE session_id = :session_id AND substr(session_id, 0, 5) = :session_id_substring ; SELECT * FROM session WHERE session_id = :session_id AND substr(session_id, 0, 5) = substr(:session_id, 0, 5) ; That will get the planner to use the partial index first, before using the session_id index. Depending on how many items are in your table, it can make your SELECTS several orders of magnitude faster. As for session plugins -- a lot of people in the web frameworks community are abandoning server side sessions for client side sessions. They are generally easier to handle state across clusters and data centers. Some server side session-like data is still needed, but it's often assembled from data in the client side. Most of the Python session plugins I've used have some sort of status check coupled with a cleanup function/middleware component to see if the object has changed at all. This way UPDATES only occur when needed. FWIW, I ended up migrating our sessions into redis. We already had redis running on the cluster, and offloading it got a lot more performance our Postgres without scaling our hardware. There just isn't much of a reason for having pg manage a simple KV store. -- 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 function
On Wed, Oct 14, 2015 at 9:38 AM, Ramesh T wrote: > Hi All, > Do we have function like regexp_substr in postgres..? > > in oracle this function seach the - from 1 to 2 and return result, > regexp_substr(PART_CATG_DESC,'[^-]+', 1, 2) > Maybe one of the functions on this page will get you what you need. http://www.postgresql.org/docs/devel/static/functions-string.html David J.
Re: [GENERAL] *** QUESTION *** After successful 'BEGIN;' command -- why PGSQL_TRANSACTION_ACTIVE and not PGSQL_TRANSACTION_INTRANS?
On 12/10/15 22:52, Steve Petrie, P.Eng. wrote: [...] BTW -- this PostgreSQL newbie (bye bye mysql) is getting a nice warm fuzzy feeling, about PostgreSQL and its amazingly helpful community :) [...] I can attempt to remedy your 'nice warm fuzzy feeling'! :-) More seriously: (1) why did you consider PostgreSQL? (2) what made you change? (3) for you, in your situation: how is the ease of use & functionality of PostgreSQL compared to MySQL? Probably best to start a new thread in pgsql-advocacy for your answers - I'm sure many people would be interested in what you have to say! 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
[GENERAL] Not storing MD5 hashed passwords
Hi, In case someone knows... Does postgres support other (stronger) hashing algorithms than MD5 to store the database passwords at disk? If not, is there any plan to move away from MD5? Thanks, Damian
Re: [GENERAL] Not storing MD5 hashed passwords
On 10/14/2015 1:31 PM, Quiroga, Damian wrote: Does postgres support other (stronger) hashing algorithms than MD5 to store the database passwords at disk? If not, is there any plan to move away from MD5? if you can read the password database, you already have superuser access to the full database so what threat does a stronger hash address? if you need stronger security, don't use passwords, use ssl certificates, or LDAP, or something. -- john r pierce, recycling bits in santa cruz
[GENERAL] BDR: pg_stat_bdr: cache lookup failed
Hi we are currently testing BDR 0.9.2 and I set up a two node cluster. From one node I can run: select * from bdr.pg_stat_bdr and it gives me the rows fine but on the other node I get the following error: ERROR: cache lookup failed for replication identifier id: 4 Any idea why? The server log produces this: 2015-10-14 21:01:13.313 UTC,"postgres","deliver",30783,"[local]",561ec296.783f,3,"SELECT",2015-10-14 21:01:10 UTC,6/5,0,ERROR,XX000,"cache lookup failed for replication identifier id: 4",,"select * from bdr.pg_stat_bdr;",,,"psql" Thanks -Selim
Re: [GENERAL] Not storing MD5 hashed passwords
On 10/14/2015 01:31 PM, Quiroga, Damian wrote: Hi, In case someone knows… Does postgres support other (stronger) hashing algorithms than MD5 to store the database passwords at disk? No. If not, is there any plan to move away from MD5? Not currently although it has been mentioned. However, you can use PAM or any number of other auth mechanisms to achieve the desired result. JD Thanks, Damian -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. New rule for social situations: "If you think to yourself not even JD would say this..." Stop and shut your mouth. It's going to be bad. -- 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] Not storing MD5 hashed passwords
On Wed, Oct 14, 2015 at 1:41 PM, John R Pierce wrote: > On 10/14/2015 1:31 PM, Quiroga, Damian wrote: > > > > Does postgres support other (stronger) hashing algorithms than MD5 to > store the database passwords at disk? > > If not, is there any plan to move away from MD5? > > There are proposals to do so, the most advanced one I know of is with SCRAM. But I don't think any of them have turned into actual plans yet. But you are not restricted to PostgreSQL's built in password authentication methods, you can use its options for PAM, LDAP, RADIUS, GSSAPI, or SSPI, in which case it doesn't store passwords at all but delegates that to someone else. if you can read the password database, you already have superuser access to > the full database > Unless you've captured a backup tape, or scraped some bits off a not-quite-degaussed-enough discarded hard drive,or any number of other things that can get you an offline copy of some (or all) of the data, but doesn't give you live access to the running database (until you hack the passwords) Cheers, Jeff
Re: [GENERAL] Not storing MD5 hashed passwords
Excellent answers. Thanks everyone. From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Jeff Janes Sent: Wednesday, October 14, 2015 7:19 PM To: John R Pierce Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Not storing MD5 hashed passwords On Wed, Oct 14, 2015 at 1:41 PM, John R Pierce mailto:pie...@hogranch.com>> wrote: On 10/14/2015 1:31 PM, Quiroga, Damian wrote: Does postgres support other (stronger) hashing algorithms than MD5 to store the database passwords at disk? If not, is there any plan to move away from MD5? There are proposals to do so, the most advanced one I know of is with SCRAM. But I don't think any of them have turned into actual plans yet. But you are not restricted to PostgreSQL's built in password authentication methods, you can use its options for PAM, LDAP, RADIUS, GSSAPI, or SSPI, in which case it doesn't store passwords at all but delegates that to someone else. if you can read the password database, you already have superuser access to the full database Unless you've captured a backup tape, or scraped some bits off a not-quite-degaussed-enough discarded hard drive,or any number of other things that can get you an offline copy of some (or all) of the data, but doesn't give you live access to the running database (until you hack the passwords) Cheers, Jeff
Re: [GENERAL] Not storing MD5 hashed passwords
On Thu, Oct 15, 2015 at 7:19 AM, Jeff Janes wrote: > On Wed, Oct 14, 2015 at 1:41 PM, John R Pierce wrote: >> >> On 10/14/2015 1:31 PM, Quiroga, Damian wrote: >> >> >> >> Does postgres support other (stronger) hashing algorithms than MD5 to >> store the database passwords at disk? >> >> If not, is there any plan to move away from MD5? > There are proposals to do so, the most advanced one I know of is with SCRAM. > But I don't think any of them have turned into actual plans yet. I would not be so sure, I heard of a patch regarding that for 9.6: https://commitfest.postgresql.org/6/320/ -- Michael -- 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] using postgresql for session
maybe we will just use beaker with our bottle framework - thought it was duplicative to have redis since we have postgres and lookup speed should be trivial since session only has a couple of small fields like account id and role Sent from Type Mail On Oct 14, 2015, 12:16, at 12:16, Jonathan Vanasco wrote: > >On Oct 7, 2015, at 11:58 AM, john.tiger wrote: > >> has anyone used postgres jsonb for holding session ? Since server >side session is really just a piece of data, why bother with special >"session" plugins and just use postgres to hold the data and retrieve >it with psycopg2 ? Maybe use some trigger if session changes?We >are using python Bottle with psycopg2 (super simple, powerful combo) - >are we missing something magical about session plugins ? > >I previously used TEXT or BLOB for holding session data, and pickled >the data. I can't remember. > >If you're going to use PostgresSQL for the session, the big performance >tip is to use partial index on the session key (assuming it's an >md5-like hash). > >So you'd want a table that is something like this: > > CREATE TABLE session( > session_id VARCHAR(32) PRIMARY KEY, > session_data TEXT > ); > CREATE INDEX idx_session_partial ON session(substr(session_id , 0,5)) > >Then query like this > > SELECT * FROM session WHERE session_id = :session_id AND >substr(session_id, 0, 5) = :session_id_substring ; > SELECT * FROM session WHERE session_id = :session_id AND >substr(session_id, 0, 5) = substr(:session_id, 0, 5) ; > >That will get the planner to use the partial index first, before using >the session_id index. Depending on how many items are in your table, >it can make your SELECTS several orders of magnitude faster. > >As for session plugins -- a lot of people in the web frameworks >community are abandoning server side sessions for client side sessions. >They are generally easier to handle state across clusters and data >centers. Some server side session-like data is still needed, but it's >often assembled from data in the client side. > >Most of the Python session plugins I've used have some sort of status >check coupled with a cleanup function/middleware component to see if >the object has changed at all. This way UPDATES only occur when >needed. > >FWIW, I ended up migrating our sessions into redis. We already had >redis running on the cluster, and offloading it got a lot more >performance our Postgres without scaling our hardware. There just >isn't much of a reason for having pg manage a simple KV store.
Re: [GENERAL] Not storing MD5 hashed passwords
On Wed, Oct 14, 2015 at 5:49 PM, Michael Paquier wrote: > On Thu, Oct 15, 2015 at 7:19 AM, Jeff Janes wrote: > > On Wed, Oct 14, 2015 at 1:41 PM, John R Pierce > wrote: > >> > >> On 10/14/2015 1:31 PM, Quiroga, Damian wrote: > >> > >> > >> > >> Does postgres support other (stronger) hashing algorithms than MD5 to > >> store the database passwords at disk? > >> > >> If not, is there any plan to move away from MD5? > > There are proposals to do so, the most advanced one I know of is with > SCRAM. > > But I don't think any of them have turned into actual plans yet. > > I would not be so sure, I heard of a patch regarding that for 9.6: > https://commitfest.postgresql.org/6/320/ Right, that is the proposal I was thinking of. I didn't think it had enough community consensus yet on that specific design to promote it to a "plan", though, rather than a proposal. I feel a bit guilty about not having done more to review it, but it is a pretty intimidating thing to review for someone not already an expert in the field. Cheers, Jeff
[GENERAL] How to get the session user in a C user defined function
I would like to get the session_user into a C char[] in a C language UDF. I have found what appears to be a function returning a Datum type called session_user, but I'm having trouble working out how to call it from within my C function. Can anyone provide some advice on how to do this? John Email: jleiseb...@bigpond.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] How to get the session user in a C user defined function
On Thu, Oct 15, 2015 at 2:38 PM, John Leiseboer wrote: > I would like to get the session_user into a C char[] in a C language UDF. I > have found what appears to be a function returning a Datum type called > session_user, but I'm having trouble working out how to call it from within > my C function. Can anyone provide some advice on how to do this? What you are looking for is in miscadmin.h: username = GetUserNameFromId(GetSessionUserId()); Regards, -- Michael -- 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] Understanding "seq scans"
Merlin Moncure writes: > On Tue, Oct 13, 2015 at 11:54 AM, Alvaro Herrera > wrote: >> Be very careful with a % at the left. The index is not going to work at >> all there. It is not the same as looking for stuff without a % at the >> left. > > Note, you can optimize LIKE '%foo%' with pg_trgm/gin indexing. > Trigram based indexing is kind of a mixed bag but is about to get a > lot faster with recent enhancements so that it should mostly match or > beat the brute force search. Right, it is indeed very efficient, in particular when trying it on PG 9.5b1! Thank you. As a bonus, it is case insensitive, so even ILIKE can take advantage of it. > This is the preferred solution if you need to do partial string matching -- > for most other cases of attribute searching I'd be looking at jsonb. Speaking of which, as this is exactly the goal of my experiments, I have now added one trigram index for each "key" of an hstore field, where the "key" is the user language and the value is a text in that language: CREATE INDEX "text_it_idx" ON test_hstore USING gin ((text->'it') gin_trgm_ops) CREATE INDEX "text_en_idx" ON test_hstore USING gin ((text->'en') gin_trgm_ops) Is this the right approach, or am I missing something clever that would allow me to have a single index? > Welcome to postgres OP! Thank you. Even if I'm not exactly new to PG, I'm very glad to have at least a little opportunity to convince my coworkers to replace an awful MySQL subsystem with a shiny new implementation based on PostgreSQL! bye, lele. -- nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia. l...@metapensiero.it | -- Fortunato Depero, 1929. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
