Re: [GENERAL] Finding matching words in a word game
Hi, I think you can make another table: Word, letter, count (word, letter - pk) In good_words add column sorted_letters. Now we can make a view based on that two tables: Word, letter, count, sorted_letters Now we need two immutable functions: 1. For given word returns sorted_letters word 2. For given word returns set of our_view Trigger on insert good_words will set sorted_letters and insert rows in word_letter_count table Using above functions... And now we have the letters: ogdssoedillrthyhtmkjilsdaio We can now say Select distinct our_view.word from second_function(the_letters) f Join our_view using(letter) Where f.sorted_letters like our_view.sorted_letters || '%' and our_view.count = f.count Now to improve performance i think would be good to put index on (letter, count) and maybe second part in where move to join part... But it would depend on explain analyze... Kind regards, Misa On Tuesday, March 5, 2013, Alexander Farber wrote: I've come up with the following INSERT trigger, if you have any improvement suggestions, please let me know (and also I wonder what to do with non-english language here, where I can't name columns a, b, etc.) - On Tue, Mar 5, 2013 at 10:59 AM, Alexander Farber alexander.far...@gmail.com javascript:; wrote: http://stackoverflow.com/questions/15220072/postgresql-and-word-games create table good_words ( word varchar(16) primary key, a integer not null default 0, b integer not null default 0, c integer not null default 0, d integer not null default 0, e integer not null default 0, /* ...skipped 20 letters... */ z integer not null default 0 ); CREATE or REPLACE FUNCTION count_letters() RETURNS trigger AS $BODY$ BEGIN SELECT into NEW.a LENGTH(NEW.word) - LENGTH(REPLACE(NEW.word, 'a', '')); SELECT into NEW.b LENGTH(NEW.word) - LENGTH(REPLACE(NEW.word, 'b', '')); SELECT into NEW.c LENGTH(NEW.word) - LENGTH(REPLACE(NEW.word, 'c', '')); SELECT into NEW.d LENGTH(NEW.word) - LENGTH(REPLACE(NEW.word, 'd', '')); /* ...skipped 20 letters... */ SELECT into NEW.z LENGTH(NEW.word) - LENGTH(REPLACE(NEW.word, 'z', '')); RETURN NEW; END; $BODY$ LANGUAGE plpgsql; CREATE TRIGGER count_letters BEFORE INSERT OR UPDATE ON good_words FOR EACH ROW EXECUTE PROCEDURE count_letters(); -- Sent via pgsql-general mailing list (pgsql-general@postgresql.orgjavascript:; ) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL connect with Visual C++
dhaval257 wrote: I am new to postgres. I am doing Project on Image processing in OpenCV(IDE i am using is Visual C++ 2010). I have downloaded PostgreSQL 8.4 and installed it successfully. I want to know how to connect postgres with visual C++. Use something more recent than 8.4 if you can. You should be able to use the C API (libpq) from C++. That's part of PostgreSQL core. Alternatively, you can use the C++ connector libpqxx (http://pqxx.org/development/libpqxx/) Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Security review
Hi all, A couple of folks in my college are doing a review of security features in various databases present today. Specifically, how these features measure up against each other. Can anyone please give me a few pointers on the features we have that MySQL doesnt? Thanks, Atri -- Regards, Atri l'apprenant -- 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 connect with Visual C++
On 3/4/2013 8:17 PM, dhaval257 wrote: I am new to postgres. I am doing Project on Image processing in OpenCV(IDE i am using is Visual C++ 2010). I have downloaded PostgreSQL 8.4 and installed it successfully. I want to know how to connect postgres with visual C++. is this app using the .net framework ? if so, you might want to use the .net postgresql database connector, http://npgsql.projects.pgfoundry.org/ if you're NOT using .net then you probably do NOT want to use that stuff. and should stick with either the libpq C bindings, or the libpqxx C++ wrapper. -- 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] PostgreSQL connect with Visual C++
* dhaval257 wrote: No It is not using .NET framework. I tried with libpq but ended up with error that Application can not run because SSLEAY32.dll is missing (something like this). So I left that thing. Have you used libpq? ssleay32.dll is part of the PostgreSQL distribution, and is installed in the /bin/ directory. Since you are getting that error for ssleay32.dll and not for libpq.dll, I assume you copied libpq.dll somewhere. Copy ssleay32.dll to the same place. You will probably get similar errors for other DLLs; all of them are likely to be in the PostgreSQL /bin/ directory. As an alternative, you can just add that directory to your %PATH% variable. -- Christian -- 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] Why does slony use a cursor? Anyone know?
From: Shaun Thomas stho...@optionshouse.com To: PostgreSQL General pgsql-general@postgresql.org Cc: Sent: Tuesday, 5 March 2013, 14:51 Subject: [GENERAL] Why does slony use a cursor? Anyone know? Hey everyone, Frankly, I'm shocked at what I just found. We did a delete last night of a few million rows, and come back this morning to find that slony is 9-hours behind. After some investigation, it became apparent that slony opens up a cursor and orders it by the log_actionseq column. Then it fetches 500 rows, and closes the cursor. So it's fetching several million rows into a cursor, to fetch 500, and then throw the rest away. That is quite possibly the least efficient manner I could think of to build a sync system, so maybe someone knows why they did it that way? At least with a regular query, it could sort by the column it wanted, and put a nifty index on it for those 500-row chunks it's grabbing. I must be missing something... What version of slony are you on? The specifics of what you mention don't sound quite right, but it sounds very much like bug 167 which was fixed in 2.1.2 if I remember correctly. Glyn -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Change owner for all tables in a database in one batch
Hello, by accident I have wrong owner for all tables in my 8.4.13 database: bukvy= select * from pg_tables where tableowner = 'skat'; schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers +++++--+- public | drupal_actions | skat | | t | f| f public | drupal_batch | skat | | t | f| f public | drupal_authmap | skat | | t | f| f public | drupal_watchdog| skat | | t | f| f (83 rows) But they actually should have the user bukvy as the owner. Is there maybe a one-liner for changing the ownership in 1 batch or should I execute alter table set owner to bukvy one by one? Thank you Alex -- 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] Change owner for all tables in a database in one batch
Alexander Farber escribió: Is there maybe a one-liner for changing the ownership in 1 batch or should I execute alter table set owner to bukvy one by one? The command REASSIGN OWNED changes ownership of all objects owned by a certain role. There's no way to restrict it to just tables, though. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Finding matching words in a word game
On Tue, Mar 5, 2013 at 8:29 PM, Alexander Farber
alexander.far...@gmail.com wrote:
is there maybe a clever way of finding all possible words
from a given set of letters by means of PostgreSQL
(i.e. inside the database vs. scanning all database
rows by a PHP script, which would take too long) -
if the dictionary is kept in a simple table like:
create table good_words (
word varchar(16) primary key,
stamp timestamp default current_timestamp
);
How many words are you looking at, in your dictionary? I wrote an
anagramming program in C++ that works off a fairly small dictionary of
common words (~60K words) and gives adequate performance without any
indexing - the time is dwarfed by just scrolling the text up the
console. The only thing I'd recommend doing differently is the
language - use one that has a proper hash/tree type, saving you the
trouble of implementing one (I implemented my own non-balancing binary
tree for the task... no wait, on examination, it seems to actually be
a linear search - and yet it has passable performance). PHP can quite
probably do everything you want here; otherwise, I'd recommend
something like Python or Pike.
Simple Python example:
words = {}
for word in dictionary: # provide a dictionary somehow - maybe from a file/db
words.setdefault(''.join(sorted(word)),[]).append(word)
# Voila! You now have your mapping. One-off initialization complete.
find_anagrams_of = stop
anagrams = words.get(''.join(sorted(find_anagrams_of)),[])
# anagrams is now a list of all known anagrams of the target -
possibly an empty list
print(anagrams)
['opts', 'post', 'pots', 'spot', 'stop', 'tops']
On my laptop, loading ~100K words took about 1 second, and the lookup
took effectively no time. I don't think there's any need for a heavy
database engine here, unless you're working with millions and millions
of words :)
Chris Angelico
--
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] Finding matching words in a word game
Thanks, will try that (the dumb approach) too :-)
Still working on my dictionary (will be auto-generated by a script).
On Wed, Mar 6, 2013 at 2:57 PM, Chris Angelico ros...@gmail.com wrote:
words = {}
for word in dictionary: # provide a dictionary somehow - maybe from a
file/db
words.setdefault(''.join(sorted(word)),[]).append(word)
# Voila! You now have your mapping. One-off initialization complete.
find_anagrams_of = stop
anagrams = words.get(''.join(sorted(find_anagrams_of)),[])
# anagrams is now a list of all known anagrams of the target -
possibly an empty list
print(anagrams)
['opts', 'post', 'pots', 'spot', 'stop', 'tops']
On my laptop, loading ~100K words took about 1 second, and the lookup
took effectively no time. I don't think there's any need for a heavy
database engine here, unless you're working with millions and millions
of words :)
Chris Angelico
--
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: [HACKERS] [GENERAL] Floating point error
On 03/05/2013 07:23 PM, Tom Lane wrote: Maciek Sakrejda m.sakre...@gmail.com writes: Thank you: I think this is what I was missing, and what wasn't clear from the proposed doc patch. But then how can pg_dump assume that it's always safe to set extra_float_digits = 3? It's been proven (don't have a link handy, but the paper is at least a dozen years old) that 3 extra digits are sufficient to accurately reconstruct any IEEE single or double float value, given properly written conversion functions in libc. So that's where that number comes from. Now, if either end is not using IEEE floats, you may or may not get equivalent results --- but it's pretty hard to make any guarantees at all in such a case. There's also gdtoa, which returns the shortest decimal representation which rounds to the same decimal number. It would print 0.1 as 0.1, but 0.1 + 0.2 as 0.30004. -- Florian Weimer / Red Hat Product Security Team -- 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] Why does slony use a cursor? Anyone know?
On 03/06/2013 04:49 AM, Glyn Astill wrote: What version of slony are you on? The specifics of what you mention don't sound quite right, but it sounds very much like bug 167 which was fixed in 2.1.2 if I remember correctly. We're on 2.1.2. Presumably, anyway. I didn't encounter the problem in stage when I set up a testbed. But it also might not be related. The problem I can tell from the logs, is that it was closing the cursor pretty much right as soon as it got the results. 75 seconds to set up a cursor of that size and then an hour to sync all the data isn't a problem. 75 seconds for every 500 rows *is*. The stage test I did didn't do that when I deleted 20M rows from a 50M row table, but I also only set it up with a single replication set. My next test will be to test with two or three replication sets that all get big deletes like that. My guess is that it can't adequately swap between them on SYNC events, so it has to rebuild the cursor every time. Either way, we're likely to be switching to an ETL system because we need to start scaling horizontally soon. Unless I want to set up a bunch of partition targets, we'll pretty much have to drop Slony then. I just want to keep it working until then. :) Thanks for the info! -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- 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] Security review
Maybe this link answers your query: http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL On 3/6/13, Atri Sharma atri.j...@gmail.com wrote: Hi all, A couple of folks in my college are doing a review of security features in various databases present today. Specifically, how these features measure up against each other. Can anyone please give me a few pointers on the features we have that MySQL doesnt? Thanks, Atri -- Regards, Atri l'apprenant -- 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
[GENERAL] FW: statement failure
Hi,
I have a dev and prod Greenplum system (4.2.2.4) that is based on PG 8.2.15
I have a function that's been working fine on dev, but when implemented in prod
had a syntax error.
I was easily able to fix by casting, but I can't see why the message didn't
show up on our dev machine.
I've reviewed the guk settings and can't find anything relevant.
Any ideas what might be different and causing problems.
Thanks in advance
ERROR: column logtransaction is of type integer but expression is of type
text
LINE 1: ...abase,loguser,loghost,logsession, logsessiontime, logtransac...
^
HINT: You will need to rewrite or cast the expression.
QUERY: insert into dba_work.table_usage_log(logtime,
logdatabase,loguser,loghost,logsession, logsessiontime, logtransaction,
logseverity, logstate, logdebug) select logtime,
logdatabase,loguser,loghost,logsession, logsessiontime, logtransaction,
logseverity, logstate, logdebug from dba_work.gp_log_master_ext where
logmessage ilike '%select%' and logmessage ilike '%from%' and logmessage not
ILIKE 'execute%' and logmessage not ILIKE '%gp_%' and logmessage not ILIKE
'%pg_%' and loguser not in ('gpadmin','gp_php_read','gpmon','ods','dlittle')
and date_trunc('day',logtime) = $1
CONTEXT: PL/pgSQL function table_usage_analyzer line 31 at SQL statement
Doug Little
Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz
Worldwide
500 W. Madison, Suite 1000 Chicago IL 60661| Office 312.260.2588 | Fax
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.commailto:douglas.lit...@orbitz.com
[cid:image001.jpg@01CE1985.EBEAC720] orbitz.comhttp://www.orbitz.com/ |
ebookers.comhttp://www.ebookers.com/ |
hotelclub.comhttp://www.hotelclub.com/ |
cheaptickets.comhttp://www.cheaptickets.com/ |
ratestogo.comhttp://www.ratestogo.com/ |
asiahotels.comhttp://www.asiahotels.com/
inline: image001.jpg
Re: [GENERAL] FW: statement failure
On Wed, Mar 6, 2013 at 9:38 AM, Little, Douglas douglas.lit...@orbitz.comwrote: Hi, ** ** I have a dev and prod Greenplum system (4.2.2.4) that is based on PG 8.2.15 I have a function that’s been working fine on dev, but when implemented in prod had a syntax error. I was easily able to fix by casting, but I can’t see why the message didn’t show up on our dev machine. can you double check pg version on both dev and prod. My guess is that prod is 8.3+ -- casting rules were tightened with 8.3. merlin
Re: [GENERAL] WAL_DEBUG
On Wed, Mar 6, 2013 at 1:31 PM, ning chan ninchan8...@gmail.com wrote: Hi, Does anyone know how to enable WAL_DEBUG? I download postgresql 9.2.3 src code and compile it as follow: modify ./src/include/pg_config_manual.h /* * Enable debugging print statements for WAL-related operations; see * also the wal_debug GUC var. */ #define WAL_DEBUG 4 ./configure --without-readline --without-zlib CPPFLAGS='-DWAL_DEBUG' Either one of those works for me. I don't see anything special log to the log file. open running pgbench -i, I see a lot of things like this in the log, once I set wal_debug: STATEMENT: alter table pgbench_accounts add primary key (aid) LOG: INSERT @ 0/18BEDC0: prev 0/18BED78; xid 1838; len 34: Btree - insert: rel 1663/16384/12678; tid 1/8 STATEMENT: alter table pgbench_accounts add primary key (aid) LOG: INSERT @ 0/18BEE08: prev 0/18BEDC0; xid 1838; len 52: Heap - insert: rel 1663/16384/12765; tid 46/29 STATEMENT: alter table pgbench_accounts add primary key (aid) LOG: INSERT @ 0/18BEE60: prev 0/18BEE08; xid 1838; len 42: Btree - insert: rel 1663/16384/12767; tid 27/140 STATEMENT: alter table pgbench_accounts add primary key (aid) LOG: INSERT @ 0/18BEEB0: prev 0/18BEE60; xid 1838; len 42: Btree - insert: rel 1663/16384/12768; tid 26/14 I even try to add a line wal_debug=number to the postgresql.conf, doesn't help either. If I include literally wal_debug=number, upon start up I get the self-explanatory error: LOG: parameter wal_debug requires a Boolean value FATAL: configuration file /tmp/data/postgresql.conf contains errors If I replace number with a valid true value (true, on, 1), then I get the above-reported WAL debugging log messages. Cheers, Jeff
Re: [GENERAL] Why does slony use a cursor? Anyone know?
On 6 March 2013 14:35, Shaun Thomas stho...@optionshouse.com wrote: On 03/06/2013 04:49 AM, Glyn Astill wrote: What version of slony are you on? The specifics of what you mention don't sound quite right, but it sounds very much like bug 167 which was fixed in 2.1.2 if I remember correctly. We're on 2.1.2. Presumably, anyway. I didn't encounter the problem in stage when I set up a testbed. But it also might not be related. The problem I can tell from the logs, is that it was closing the cursor pretty much right as soon as it got the results. 75 seconds to set up a cursor of that size and then an hour to sync all the data isn't a problem. 75 seconds for every 500 rows *is*. The stage test I did didn't do that when I deleted 20M rows from a 50M row table, but I also only set it up with a single replication set. My next test will be to test with two or three replication sets that all get big deletes like that. My guess is that it can't adequately swap between them on SYNC events, so it has to rebuild the cursor every time. Either way, we're likely to be switching to an ETL system because we need to start scaling horizontally soon. Unless I want to set up a bunch of partition targets, we'll pretty much have to drop Slony then. I just want to keep it working until then. :) A cursor can make use of indexes for sorting, so an index on sl_log_1/2(log_actionseq) may help. Regards, Dean -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] ERROR: syntax error at or near :
Hi all, I have a text file, and I need to update the value of an element in a table with the contents of this text file. Following the instructions at http://stackoverflow.com/questions/10968039/postgresql-inserting-value-of-a-column-from-a-file I tried this, but get the error below, which I do not understand. Can anyone explain what might be going wrong, and what I should do instead? patricia=# \set content `cat /tmp/certificates.txt` patricia=# update property set value = :'content' where key = 'patricia.home.security.cacerts'; ERROR: syntax error at or near : LINE 1: update property set value = :'content' where key = 'patricia... ^ Regards, Graham -- smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] ERROR: syntax error at or near :
Graham -- From: Graham Leggett minf...@sharp.fm To: pgsql-general@postgresql.org pgsql-general@postgresql.org Sent: Wednesday, March 6, 2013 2:41 PM Subject: [GENERAL] ERROR: syntax error at or near : Hi all, I have a text file, and I need to update the value of an element in a table with the contents of this text file. Following the instructions at http://stackoverflow.com/questions/10968039/postgresql-inserting-value-of-a-column-from-a-file I tried this, but get the error below, which I do not understand. Can anyone explain what might be going wrong, and what I should do instead? patricia=# \set content `cat /tmp/certificates.txt` patricia=# update property set value = :'content' where key = 'patricia.home.security.cacerts'; ERROR: syntax error at or near : LINE 1: update property set value = :'content' where key = 'patricia... ^ The colon (:) is not needed, just remove it. A pair of colons is used to indicate a cast of a value; off hand I am not coming up with any use of a colon in basic SQL. Greg W. -- 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] ERROR: syntax error at or near :
2013/3/7 Graham Leggett minf...@sharp.fm: Hi all, I have a text file, and I need to update the value of an element in a table with the contents of this text file. Following the instructions at http://stackoverflow.com/questions/10968039/postgresql-inserting-value-of-a-column-from-a-file I tried this, but get the error below, which I do not understand. Can anyone explain what might be going wrong, and what I should do instead? patricia=# \set content `cat /tmp/certificates.txt` patricia=# update property set value = :'content' where key = 'patricia.home.security.cacerts'; ERROR: syntax error at or near : LINE 1: update property set value = :'content' where key = 'patricia... That should work... Which psql version are you using, and what is the table definition? Does the same error occur if you attempt to insert data from a different text file? Regards Ian Barwick -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: syntax error at or near :
On 03/06/2013 02:41 PM, Graham Leggett wrote: Hi all, I have a text file, and I need to update the value of an element in a table with the contents of this text file. Following the instructions at http://stackoverflow.com/questions/10968039/postgresql-inserting-value-of-a-column-from-a-file I tried this, but get the error below, which I do not understand. Can anyone explain what might be going wrong, and what I should do instead? patricia=# \set content `cat /tmp/certificates.txt` patricia=# update property set value = :'content' where key = 'patricia.home.security.cacerts'; ERROR: syntax error at or near : LINE 1: update property set value = :'content' where key = 'patricia... ^ Worked here for me. Is the field you are trying to set really named value? Even though VALUE is marked non-reserved here: http://www.postgresql.org/docs/9.2/interactive/sql-keywords-appendix.html you might want to follow the advice from above link: As a general rule, if you get spurious parser errors for commands that contain any of the listed key words as an identifier you should try to quote the identifier to see if the problem goes away. Regards, Graham -- -- 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] ERROR: syntax error at or near :
Greg, 2013/3/7 Greg Williamson gwilliamso...@yahoo.com: Graham -- (...) The colon (:) is not needed, just remove it. A pair of colons is used to indicate a cast of a value; off hand I am not coming up with any use of a colon in basic SQL. This is psql-specific syntax; the colon should cause the value of the psql variable 'content' to be interpreted; without it, the string 'content' would be inserted. See: http://www.postgresql.org/docs/current/interactive/app-psql.html#APP-PSQL-INTERPOLATION Regards Ian Barwick -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] WAL_DEBUG
On Wed, Mar 6, 2013 at 2:38 PM, ning chan ninchan8...@gmail.com wrote: Hi Jeff, Thanks for your reply. this is the error message i got: -bash-4.1$ pg_ctl start server starting -bash-4.1$ LOG: unrecognized configuration parameter wal_debug in file /usr/local/pgsql/data/postgresql.conf line 162 FATAL: configuration file /usr/local/pgsql/data/postgresql.conf contains errors This means the server wasn't compiled with WAL_DEBUG defined. You have to do both things, first compile with WAL_DEBUG, and then set the wal_debug guc to on. One question: Do i need to uninstall first before I gmake install again? I've never bothered to do that and it never gave me any problems (but I wouldn't so on a running production system). You do need to run make maintainer-clean before you redo the ./config and build and install, though. If so, any idean how to uninstall it? If you originally installed from source yourself using the normal build tools, uninstalling should need nothing more than removing the directory that --prefix was originally set to. If you had built other things that linked against the installation (DBD::Pg, for example) they will be broken by this, but they should work again once you redo the install. If you installed some other way, I don't know. Cheers, Jeff
Re: [GENERAL] ERROR: syntax error at or near :
On 03/06/2013 03:04 PM, Greg Williamson wrote: Graham -- From: Graham Leggett minf...@sharp.fm To: pgsql-general@postgresql.org pgsql-general@postgresql.org Sent: Wednesday, March 6, 2013 2:41 PM Subject: [GENERAL] ERROR: syntax error at or near : Hi all, I have a text file, and I need to update the value of an element in a table with the contents of this text file. Following the instructions at http://stackoverflow.com/questions/10968039/postgresql-inserting-value-of-a-column-from-a-file I tried this, but get the error below, which I do not understand. Can anyone explain what might be going wrong, and what I should do instead? patricia=# \set content `cat /tmp/certificates.txt` patricia=# update property set value = :'content' where key = 'patricia.home.security.cacerts'; ERROR: syntax error at or near : LINE 1: update property set value = :'content' where key = 'patricia... ^ The colon (:) is not needed, just remove it. A pair of colons is used to indicate a cast of a value; off hand I am not coming up with any use of a colon in basic SQL. http://www.postgresql.org/docs/9.2/interactive/app-psql.html#APP-PSQL-INTERPOLATION Greg W. -- 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] ERROR: syntax error at or near :
On 07 Mar 2013, at 1:05 AM, Ian Lawrence Barwick barw...@gmail.com wrote:
Can anyone explain what might be going wrong, and what I should do instead?
patricia=# \set content `cat /tmp/certificates.txt`
patricia=# update property set value = :'content' where key =
'patricia.home.security.cacerts';
ERROR: syntax error at or near :
LINE 1: update property set value = :'content' where key = 'patricia...
That should work…
I have used this before the last time I needed to do this, and it worked then.
No idea why it doesn't work now, and the error message is of no help. Is there
a log file or some kind of forensic debugging that I can switch on to coax some
kind of useful out from psql?
Which psql version are you using, and what is the table definition?
Version as below, from RHEL6:
psql (PostgreSQL) 8.4.13
contains support for command-line editing
patricia=# \d property
Table public.property
Column| Type| Modifiers
-+---+
property_id | integer | not null default
nextval(('property_SEQ'::text)::regclass)
key | character varying | not null
value | character varying |
Indexes:
property_pkey PRIMARY KEY, btree (property_id)
property_index btree (key)
Does the same error occur if you attempt to insert data from a
different text file?
I haven't tried. This is a long blob of PEM encoded certificates, so trying to
narrow down a troublesome character will be hard.
Is there some kind of restriction on character data that can be imported into
psql?
Regards,
Graham
--
smime.p7s
Description: S/MIME cryptographic signature
Re: [GENERAL] ERROR: syntax error at or near :
Thanks for the link / explanation -- hadn't seen this use before. GW - Original Message - From: Adrian Klaver adrian.kla...@gmail.com To: Greg Williamson gwilliamso...@yahoo.com Cc: pgsql-general@postgresql.org pgsql-general@postgresql.org Sent: Wednesday, March 6, 2013 3:13 PM Subject: Re: [GENERAL] ERROR: syntax error at or near : On 03/06/2013 03:04 PM, Greg Williamson wrote: Graham -- From: Graham Leggett minf...@sharp.fm To: pgsql-general@postgresql.org pgsql-general@postgresql.org Sent: Wednesday, March 6, 2013 2:41 PM Subject: [GENERAL] ERROR: syntax error at or near : Hi all, I have a text file, and I need to update the value of an element in a table with the contents of this text file. Following the instructions at http://stackoverflow.com/questions/10968039/postgresql-inserting-value-of-a-column-from-a-file I tried this, but get the error below, which I do not understand. Can anyone explain what might be going wrong, and what I should do instead? patricia=# \set content `cat /tmp/certificates.txt` patricia=# update property set value = :'content' where key = 'patricia.home.security.cacerts'; ERROR: syntax error at or near : LINE 1: update property set value = :'content' where key = 'patricia... ^ The colon (:) is not needed, just remove it. A pair of colons is used to indicate a cast of a value; off hand I am not coming up with any use of a colon in basic SQL. http://www.postgresql.org/docs/9.2/interactive/app-psql.html#APP-PSQL-INTERPOLATION Greg W. -- 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 -- 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] ERROR: syntax error at or near :
2013/3/7 Graham Leggett minf...@sharp.fm: (...) Which psql version are you using, and what is the table definition? Version as below, from RHEL6: psql (PostgreSQL) 8.4.13 Aha, there is your problem: testdb=# SELECT version(); version - PostgreSQL 8.4.16 on x86_64-unknown-linux-gnu, compiled by GCC gcc (SUSE Linux) 4.7.1 20120723 [gcc-4_7-branch revision 189773], 64-bit (1 row) testdb=# \set content `cat /tmp/hello.txt` testdb=# CREATE TABLE interpolation (value TEXT); CREATE TABLE testdb=# INSERT INTO interpolation VALUES (:'content'); ERROR: syntax error at or near : LINE 1: INSERT INTO interpolation VALUES (:'content'); I.e. that syntax is not supported in 8.4. You'll need to do this: \set content `cat /tmp/certificates.txt` patricia=# update property set value = :content where key = 'patricia.home.security.cacerts'; See: http://www.postgresql.org/docs/8.4/interactive/app-psql.html#AEN71586 (...) Does the same error occur if you attempt to insert data from a different text file? I haven't tried. This is a long blob of PEM encoded certificates, so trying to narrow down a troublesome character will be hard. I was thinking more along the lines of using a small text file to identify whether the problem is with the data, or something else preventing you use this syntax (which as it turns out is the PostreSQL version). Is there some kind of restriction on character data that can be imported into psql? AFAIK only NUL bytes can't be imported this way, see: http://www.postgresql.org/docs/current/interactive/app-psql.html#APP-PSQL-INTERPOLATION Regards Ian Barwick -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] round returns -0
I'm taking an standard deviation of a population and subtracting it from the average of the same population and rounding the result. Sometimes that result is negative and rounding it returns (or shows up as) a negative zero (-0) in a SELECT. basically: SELECT client_name, avg(rpt_cnt), stddev_pop(rpt_cnt), round(avg(rpt_cnt) - stddev_pop(rpt_cnt)) from client_counts group by client_name and what I sometimes get is : client_name | a dp number | a dp number | -0 In postgresql-world, is -0 = 0? Can I use that negative 0 in further calculations without fear? Is this a bug? pg version is 9.2 OS is Windows 2003. Thanks, Wade Dare Committed to striving for an effort to try... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Trust intermediate CA for client certificates
I am trying to configure PostgreSQL 8.4 to trust an intermediate CA for client certificate validation -- without trusting everything signed by the root CA (or a different intermediate CA). Given the following CA hierarchy, for example, I would like to trust *only* client certificates signed by the client CA. +-+ | Root CA | +-+ /\ / \ /\ / \ /\ / \ /\ / \ +---++---+ | Server CA || Client CA | +---++---+ I expected that I could simply use the client CA certificate as $PGDATA/root.crt, but this does not work; I get an unknown ca error. AFAICT, there is absolutely no way to make PostgreSQL trust a CA that is not a self-signed root CA. I can connect successfully if I add the root CA certificate to the root.crt file, but would effectively trust any certificate signed by the root CA or any of its subsidiaries, something that I absolutely do not want. Am I missing something? (And yes I have read the documentation, several times over. It talks about adding the intermediate CA certificate(s) to the certificate chain presented by the client, so that the server can complete the chain between the client certificate and the trusted root CA. The use case described above is not discussed, even though it's pretty fundamental to the PKI trust model.) Thanks! -- Ian Pilcher arequip...@gmail.com Sometimes there's nothing left to do but crash and burn...or die trying. -- 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] round returns -0
Le 2013-03-06 à 21:42, Tony Dare a écrit : I'm taking an standard deviation of a population and subtracting it from the average of the same population and rounding the result. Sometimes that result is negative and rounding it returns (or shows up as) a negative zero (-0) in a SELECT. basically: SELECT client_name, avg(rpt_cnt), stddev_pop(rpt_cnt), round(avg(rpt_cnt) - stddev_pop(rpt_cnt)) from client_counts group by client_name and what I sometimes get is : client_name | a dp number | a dp number | -0 In postgresql-world, is -0 = 0? Can I use that negative 0 in further calculations without fear? Is this a bug? This is related to the recent discussion of floating point values on this mailing list. You can read more about IEEE 754 and whether 0 == -0 on Wikipedia: https://en.wikipedia.org/wiki/Signed_zero#Comparisons According to that article, IEEE 754 specifies that 0 == -0 in Java/C/etc. Hope that helps! François Beausoleil smime.p7s Description: S/MIME cryptographic signature
