Re: [GENERAL] Vacuum analyze verbose output
Thanks a lot Glyn. --- On Wed, 19/12/12, Glyn Astill wrote: From: Glyn Astill Subject: Re: [GENERAL] Vacuum analyze verbose output To: "Anjali Arora" , "pgsql-general@postgresql.org" Date: Wednesday, 19 December, 2012, 3:19 PM > From: Anjali Arora >To: pgsql-general@postgresql.org >Sent: Wednesday, 19 December 2012, 9:14 >Subject: [GENERAL] Vacuum analyze verbose output > > >Hi all, > > >I ran following command on 8.2.2 postgresql: > > > psql -p port dbname -c "vacuum analyze verbose" > > >last few lines from "vacuum analyze verbose" output: > > >DETAIL: A total of 2336 page slots are in use (including overhead). >2336 page slots are required to track all free space. >Current limits are: 153600 page slots, 1000 relations, using 965 kB. > > >After upgrade to postgresql 9.0.4 I am not getting this output as the part of >"vacuum analyze verbose" output. > > >Can anybody please let me know where can I find this information in postgresql >9.0.4. I'm not sure you can, as of 8.4 free space tracking was made automatic, so you no longer need to track max_fsm_pages and max_fsm_relations See: http://www.postgresql.org/docs/8.4/static/release-8-4.html
Re: [GENERAL] DONT_CARE Aggregate
On Wed, Dec 19, 2012 at 5:28 PM, Robert James wrote: > And even better: > An aggregate which will return the first instance that meets a certain > predicate? Take a look at DISTINCT ON. -- Sergey Konoplev Database and Software Architect http://www.linkedin.com/in/grayhemp Phones: USA +1 415 867 9984 Russia, Moscow +7 901 903 0499 Russia, Krasnodar +7 988 888 1979 Skype: gray-hemp Jabber: 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
[GENERAL] Re: [COMMITTERS] pgsql: Allow a streaming replication standby to follow a timeline switc
On 18.12.2012 13:42, hubert depesz lubaczewski wrote: In pg_log on ubuntu2 I see: 2012-12-18 12:41:34.428 CET [unknown]@[unknown] 1685 LOG: connection received: host=172.28.173.142 port=45842 2012-12-18 12:41:34.430 CET replication@[unknown] 1685 172.28.173.142(45842) LOG: replication connection authorized: user=replication 2012-12-18 12:41:34.432 CET replication@[unknown] 1685 172.28.173.142(45842) ERROR: requested WAL segment 00020015 has already been removed 2012-12-18 12:41:34.433 CET replication@[unknown] 1685 172.28.173.142(45842) LOG: disconnection: session time: 0:00:00.005 user=replication database= host=172.28.173.142 port=45842 Something looks weird. To put it lightly. Hmm, that's a different error than you got before. Thom also reported a "requested WAL segment ... has already been removed" error, but in his test case, and as far as I could reproduce it, the error doesn't reoccur when the standby reconnects. In other words, it eventually worked despite that error. In any case, I just committed a fix for the scenario that Thom reported. Can you try again with a fresh checkout? - Heikki -- 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] DONT_CARE Aggregate
Sergey - That's an interesting option, but I'm not sure how to use it as an aggregate. Could you give an example? On 12/20/12, Sergey Konoplev wrote: > On Wed, Dec 19, 2012 at 5:28 PM, Robert James > wrote: >> And even better: >> An aggregate which will return the first instance that meets a certain >> predicate? > > Take a look at DISTINCT ON. > > -- > Sergey Konoplev > Database and Software Architect > http://www.linkedin.com/in/grayhemp > > Phones: > USA +1 415 867 9984 > Russia, Moscow +7 901 903 0499 > Russia, Krasnodar +7 988 888 1979 > > Skype: gray-hemp > Jabber: 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] Any experience with Drobo SAN and PG?
On Wed, Dec 19, 2012 at 3:14 PM, John R Pierce wrote: > I've got it formatted as RaidZ (ZFS's equivalent of raid5) and have no > problem hitting 80MB/second over gigE with SMB.Now, I've not yet tested > iscsi on it. > I'm interested to know how heavy ZFS usage holds up with only 8GB of kernel memory. Is FreeNAS up to kernel 9.x yet? I haven't followed FreeNAS for a while. My smallest RAM machine with ZFS has 16GB and I am *loving* it. I wish it was easier to make it the boot default file system too!
Re: [GENERAL] DONT_CARE Aggregate
On Thu, Dec 20, 2012 at 5:45 AM, Robert James wrote: > Sergey - That's an interesting option, but I'm not sure how to use it > as an aggregate. Could you give an example? > Here is an example: buildinghac=> SELECT itemnbr, buildingnbr FROM Actionitems ORDER BY buildingnbr LIMIT 10; itemnbr | buildingnbr -+- 1181 | B-0106 363 | B-0106 185 | B-0106 483 | B-0106 67 | B-0106 125 | B-0106 303 | B-0106 245 | B-0106 68 | B-0107 304 | B-0107 (10 rows) buildinghac=> SELECT DISTINCT ON ( buildingnbr ) itemnbr, buildingnbr FROM Actionitems ORDER BY buildingnbr LIMIT 10; itemnbr | buildingnbr -+- 245 | B-0106 364 | B-0107 1170 | B-0111 361 | B-0112 128 | B-0116 1013 | B-0117 129 | B-0118 368 | B-0300 1141 | B-0307 74 | B-0423 (10 rows) -- Regards, Richard Broersma Jr.
Re: [GENERAL] DONT_CARE Aggregate
I see. What if I need to do this along with an Aggregate Query. Eg something like: SELECT x,y,z, MAX(a), MAX(b), DONT_CARE_AS_LONG_AS_NOT_NULL(c), DONT_CAR_AS_LONG_AS_P_IS_TRUE(d,p) ... GROUP BY x,y,z On 12/20/12, Richard Broersma wrote: > On Thu, Dec 20, 2012 at 5:45 AM, Robert James > wrote: > >> Sergey - That's an interesting option, but I'm not sure how to use it >> as an aggregate. Could you give an example? >> > > Here is an example: > > buildinghac=> > SELECT itemnbr, buildingnbr > FROM Actionitems > ORDER BY buildingnbr >LIMIT 10; > itemnbr | buildingnbr > -+- > 1181 | B-0106 > 363 | B-0106 > 185 | B-0106 > 483 | B-0106 > 67 | B-0106 > 125 | B-0106 > 303 | B-0106 > 245 | B-0106 > 68 | B-0107 > 304 | B-0107 > (10 rows) > > > buildinghac=> > > SELECT DISTINCT ON ( buildingnbr ) > itemnbr, buildingnbr > FROM Actionitems > ORDER BY buildingnbr >LIMIT 10; > itemnbr | buildingnbr > -+- > 245 | B-0106 > 364 | B-0107 > 1170 | B-0111 > 361 | B-0112 > 128 | B-0116 > 1013 | B-0117 > 129 | B-0118 > 368 | B-0300 > 1141 | B-0307 > 74 | B-0423 > (10 rows) > > > -- > Regards, > Richard Broersma Jr. > -- 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] DONT_CARE Aggregate
On Thu, Dec 20, 2012 at 12:00 PM, Robert James wrote: > I see. What if I need to do this along with an Aggregate Query. Eg > something like: > > SELECT x,y,z, MAX(a), MAX(b), DONT_CARE_AS_LONG_AS_NOT_NULL(c), > DONT_CAR_AS_LONG_AS_P_IS_TRUE(d,p) > ... > GROUP BY x,y,z > > ah, I get what you're trying to do. If you truly don't care about the value of C, then just use MIN() or MAX(). for the conditional part, use a CASE statement, along with MAX or MIN, like this: SELECT MIN(CASE WHEN P=TRUE THEN D ELSE NULL END) Because MIN() and MAX() ignore NULL values. (Except for the special case where all the values are null.) -- e-Mail is the equivalent of a postcard written in pencil. This message may not have been sent by me, or intended for you. It may have been read or even modified while in transit. e-Mail disclaimers have the same force in law as a note passed in study hall. If your corporate attorney says that you need an disclaimer in your signature, you need a new corporate attorney.
Re: [GENERAL] DONT_CARE Aggregate
On Thu, Dec 20, 2012 at 3:28 AM, Robert James wrote: > Is there an aggregate that will return an arbitrary instance? That is, > not necessarily the max or min, just any one? (Which might perform > better than max or min) > > More importantly: > Is there one which will return an arbitrary instance as long as it's not NULL There's an extension on PGXN which implements first()/last() aggregates in C: http://pgxn.org/dist/first_last_agg/ It should be slightly faster than min()/max(), but the difference is probably not significant in more complex queries. Regards, Marti -- 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] DONT_CARE Aggregate
Marti Raudsepp writes: > On Thu, Dec 20, 2012 at 3:28 AM, Robert James wrote: >> Is there an aggregate that will return an arbitrary instance? That is, >> not necessarily the max or min, just any one? (Which might perform >> better than max or min) >> >> More importantly: >> Is there one which will return an arbitrary instance as long as it's not NULL > There's an extension on PGXN which implements first()/last() > aggregates in C: http://pgxn.org/dist/first_last_agg/ > It should be slightly faster than min()/max(), but the difference is > probably not significant in more complex queries. Another thing to consider is that the presence of any "generic" aggregate forces a full-table scan, since the system doesn't know that the aggregate has any particular behavior. MIN/MAX on the other hand can be optimized into index probes, if they are on indexed columns. If the query otherwise uses only MIN/MAX aggregates, it's not hard to believe that adding a FIRST() or LAST() instead of a MIN/MAX aggregate could make the query significantly slower, not faster. However, if you're targeting queries containing a variety of aggregates, or if any of them are on unindexed columns, then this special case may not be of much interest. 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] Any experience with Drobo SAN and PG?
On 12/20/2012 5:56 AM, Vick Khera wrote: I'm interested to know how heavy ZFS usage holds up with only 8GB of kernel memory. Is FreeNAS up to kernel 9.x yet? I haven't followed FreeNAS for a while. My smallest RAM machine with ZFS has 16GB and I am *loving* it. I wish it was easier to make it the boot default file system too! its currently FreeBSD 8.3-RELEASE-p4, and my home (1-2 desktops, 2-3 laptops, a tablet and a couple android phones, and a A/V system playing video) hardly counts as heavy use. there's rarely more than a couple files being read/written at the same 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] Log rolling
Is there any sql/psql command to force a log roll (i.e. start a new log file)? alan -- 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] Log rolling
On 12/20/2012 02:08 PM, Alan Nilsson wrote: Is there any sql/psql command to force a log roll (i.e. start a new log file)? http://www.postgresql.org/docs/9.2/interactive/functions-admin.html pg_rotate_logfile() alan -- 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
[GENERAL] Implicit transaction not rolling back after error
I'm having difficulty locating the source of a problem our webapp has been running into. Multiple Google searches have failed me, so I'm hoping someone here can help troubleshoot. When some clients (psql, the webapp) connect to our production database, they become stuck in an aborted transaction after any failed statement. For example: $ psql --version psql (PostgreSQL) 9.0.5 $ psql test psql (9.0.5) SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) Type "help" for help. test=> SELECT foo; ERROR: column "foo" does not exist LINE 1: SELECT foo; ^ test=> SELECT VERSION(); ERROR: current transaction is aborted, commands ignored until end of transaction block Of course, there is no explicit transaction around the first statement, but no commands can be issued until after a ROLLBACK. Stangely, I don't encounter this issue if I connect directly from my development machine. $ psql --version psql (PostgreSQL) 9.2.1 $ psql --host $HOST test psql (9.2.1, server 9.0.5) WARNING: psql version 9.2, server version 9.0. Some psql features might not work. SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) Type "help" for help. test=> SELECT foo; ERROR: column "foo" does not exist LINE 1: SELECT foo; ^ test=> SELECT VERSION(); version - PostgreSQL 9.0.5 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit (1 row) I also don't encounter the issue if I connect to the running 9.2 daemon on my development machine either. Our production webapp also suffers from this problem (which is how we noticed it in the first place). It connects to the PostgreSQL daemon through a Ruby library (pg) which wraps libpq5. I assumed the only commonality between psql and our application was libpq5, so upgraded it to a later version (9.2.2) with no luck. Does anyone know what this might be? Is it a bug? A feature? A configuration issue? I'm kind of at a loss at this point. Thanks in advance for your help. -- Stephen Touset Senior Software Engineer stephen.tou...@onelogin.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] Implicit transaction not rolling back after error
On 12/20/2012 03:11 PM, Stephen Touset wrote: I'm having difficulty locating the source of a problem our webapp has been running into. Multiple Google searches have failed me, so I'm hoping someone here can help troubleshoot. I also don't encounter the issue if I connect to the running 9.2 daemon on my development machine either. Our production webapp also suffers from this problem (which is how we noticed it in the first place). It connects to the PostgreSQL daemon through a Ruby library (pg) which wraps libpq5. I assumed the only commonality between psql and our application was libpq5, so upgraded it to a later version (9.2.2) with no luck. Does anyone know what this might be? Is it a bug? A feature? A configuration issue? I'm kind of at a loss at this point. Well for the psql case check out: http://www.postgresql.org/docs/9.0/interactive/app-psql.html " AUTOCOMMIT When on (the default), each SQL command is automatically committed upon successful completion. To postpone commit in this mode, you must enter a BEGIN or START TRANSACTION SQL command. When off or unset, SQL commands are not committed until you explicitly issue COMMIT or END. The autocommit-off mode works by issuing an implicit BEGIN for you, just before any command that is not already in a transaction block and is not itself a BEGIN or other transaction-control command, nor a command that cannot be executed inside a transaction block (such as VACUUM). Note: In autocommit-off mode, you must explicitly abandon any failed transaction by entering ABORT or ROLLBACK. Also keep in mind that if you exit the session without committing, your work will be lost. Note: The autocommit-on mode is PostgreSQL's traditional behavior, but autocommit-off is closer to the SQL spec. If you prefer autocommit-off, you might wish to set it in the system-wide psqlrc file or your ~/.psqlrc file. " Not sure about the Ruby case. Thanks in advance for your help. -- 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] Implicit transaction not rolling back after error
On Dec 20, 2012, at 3:27 PM, Adrian Klaver wrote: > When on (the default), each SQL command is automatically committed upon > successful completion. To postpone commit in this mode, you must enter a > BEGIN or START TRANSACTION SQL command. When off or unset, SQL commands are > not committed until you explicitly issue COMMIT or END. The autocommit-off > mode works by issuing an implicit BEGIN for you, just before any command that > is not already in a transaction block and is not itself a BEGIN or other > transaction-control command, nor a command that cannot be executed inside a > transaction block (such as VACUUM). > > Note: In autocommit-off mode, you must explicitly abandon any failed > transaction by entering ABORT or ROLLBACK. Also keep in mind that if you exit > the session without committing, your work will be lost. > Note: The autocommit-on mode is PostgreSQL's traditional behavior, but > autocommit-off is closer to the SQL spec. If you prefer autocommit-off, you > might wish to set it in the system-wide psqlrc file or your ~/.psqlrc file. In this case, not only is AUTOCOMMIT set to "on", but I wasn't even able to turn it off for testing purposes. test=# SET AUTOCOMMIT TO off; ERROR: SET AUTOCOMMIT TO OFF is no longer supported So yes, AUTOCOMMIT is definitely on. -- Stephen Touset Senior Software Engineer stephen.tou...@onelogin.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] Implicit transaction not rolling back after error
On Dec 20, 2012, at 3:27 PM, Adrian Klaver wrote: > When on (the default), each SQL command is automatically committed upon > successful completion. To postpone commit in this mode, you must enter a > BEGIN or START TRANSACTION SQL command. When off or unset, SQL commands are > not committed until you explicitly issue COMMIT or END. The autocommit-off > mode works by issuing an implicit BEGIN for you, just before any command that > is not already in a transaction block and is not itself a BEGIN or other > transaction-control command, nor a command that cannot be executed inside a > transaction block (such as VACUUM). > > Note: In autocommit-off mode, you must explicitly abandon any failed > transaction by entering ABORT or ROLLBACK. Also keep in mind that if you exit > the session without committing, your work will be lost. > Note: The autocommit-on mode is PostgreSQL's traditional behavior, but > autocommit-off is closer to the SQL spec. If you prefer autocommit-off, you > might wish to set it in the system-wide psqlrc file or your ~/.psqlrc file. Actually, you may be onto something. test=> COMMIT; WARNING: there is no transaction in progress COMMIT onelogin_production=> SHOW AUTOCOMMIT; autocommit on (1 row) test=> COMMIT; COMMIT If I try to do a bare "COMMIT", I get a warning that there's no transaction in progress. But if I do a simple SHOW, and COMMIT afterward, I get no such warning (indicating that I'm still inside of an uncommitted transaction). However, obviously, the client is telling me explicitly in the provided log that AUTOCOMMIT is on. -- Stephen Touset Senior Software Engineer stephen.tou...@onelogin.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] Implicit transaction not rolling back after error
On 12/20/2012 04:33 PM, Stephen Touset wrote: On Dec 20, 2012, at 3:27 PM, Adrian Klaver wrote: When on (the default), each SQL command is automatically committed upon successful completion. To postpone commit in this mode, you must enter a BEGIN or START TRANSACTION SQL command. When off or unset, SQL commands are not committed until you explicitly issue COMMIT or END. The autocommit-off mode works by issuing an implicit BEGIN for you, just before any command that is not already in a transaction block and is not itself a BEGIN or other transaction-control command, nor a command that cannot be executed inside a transaction block (such as VACUUM). Note: In autocommit-off mode, you must explicitly abandon any failed transaction by entering ABORT or ROLLBACK. Also keep in mind that if you exit the session without committing, your work will be lost. Note: The autocommit-on mode is PostgreSQL's traditional behavior, but autocommit-off is closer to the SQL spec. If you prefer autocommit-off, you might wish to set it in the system-wide psqlrc file or your ~/.psqlrc file. In this case, not only is AUTOCOMMIT set to "on", but I wasn't even able to turn it off for testing purposes. test=# SET AUTOCOMMIT TO off; ERROR: SET AUTOCOMMIT TO OFF is no longer supported So yes, AUTOCOMMIT is definitely on. What does \set show when entered from the psql command line? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Using POSIX Regular Expressions on xml type fields gives inconsistent results
I have a table with an xml type column, and while I can make regex
queries like this successfully:
=> select id from form_d where 'kumar' ~* any(
CAST(xpath('//PersonsList/PersonName/LastName/text()', data) as TEXT[]) );
id
--
97e1541b-27f4-4d95-beb5-2f67830ebc48
(1 row)
and
=> select id from form_d where '(kumar|gonzales)' ~* any(
CAST(xpath('//PersonsList/PersonName/LastName/text()', data) as TEXT[]) );
id
--
aea32e7e-f422-405c-953b-86fe3c8c1e30
97e1541b-27f4-4d95-beb5-2f67830ebc48
(2 rows)
I.e., they are successful in that the last names in the xml data are
"Kumar" and "Gonzales", so the ~* operator handled the case comparison
correctly, and the (|) grouping also found the two rows where the
corresponding xml had "Kumar" and "Gonzales" in the PersonList attribute.
But if I change the expression to ask for all last names beginning with
"Kuma" or "Gonza", like this, the query returns no matches:
=> select id from form_d where '^kuma' ~* any(
CAST(xpath('//PersonsList/PersonName/LastName/text()', data) as TEXT[]) );
id
(0 rows)
=> select id from form_d where '^gonza' ~* any(
CAST(xpath('//PersonsList/PersonName/LastName/text()', data) as TEXT[]) );
id
(0 rows)
=> select id from form_d where '^(kuma|gonza)' ~* any(
CAST(xpath('//PersonsList/PersonName/LastName/text()', data) as TEXT[]) );
id
(0 rows)
Why is that?
--
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 POSIX Regular Expressions on xml type fields gives inconsistent results
>
> I have a table with an xml type column, and while I can make regex queries
> like this successfully:
>
> => select id from form_d where 'kumar' ~* any(
> CAST(xpath('//PersonsList/PersonName/LastName/text()', data) as TEXT[])
> );
It took me way too long to figure this out:
SELECT 'text to search against' ~* 'regular expression'
The text to be searched must be on the left-hand side of the operator and
thus the regular expression operators cannot be used with "ANY/ALL" because
they require that the text to be search exist on the right-hand side of the
operator.
I'll let others respond as to the difficulty of implementing a LHS-Array
version of ANY/ALL
David J.
--
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 POSIX Regular Expressions on xml type fields gives inconsistent results
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Denis Papathanasiou
> Sent: Thursday, December 20, 2012 7:56 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Using POSIX Regular Expressions on xml type fields
gives
> inconsistent results
>
> I have a table with an xml type column, and while I can make regex queries
> like this successfully:
>
> => select id from form_d where 'kumar' ~* any(
> CAST(xpath('//PersonsList/PersonName/LastName/text()', data) as TEXT[])
> );
>id
> --
> 97e1541b-27f4-4d95-beb5-2f67830ebc48
> (1 row)
For educational purposes:
SELECT 'kumar' ~* 'Kumar' -> TRUE (simplest possible)
SELECT 'kumar' ~* any ( ARRAY['Kumar','Gonzales']::text[] ) -> TRUE (ok,
arrays work)
[let's play with partial matching now that we know full/basic matching
works]
SELECT 'kuma' ~* 'Kumar' -> FALSE (wait, if this is broken then
regression tests would had to have caught this...I must have something
wrong)
[optionally go look at " ~* " in the documentation at this point; or just
try a simple flip-flop of the expression]
SELECT 'Kumar' ~* 'kuma' -> TRUE !!! (ah ha! I had the order reversed)
SELECT any( ARRAY['Kumar','Gozales']::text ) ... wait...ANY doesn't work on
the other side... :(
[At this point I'd confirm or question why ANY hasn't been made to go both
ways but also realize that I will have to approach this in a different way
to achieve my goal.]
If you come across a problem with basic features of any software it is
likely you are confused rather than the system providing inconsistent
results. Take the feature in question and make sure you understand the more
basic uses (while omitting as many moving parts as possible - in this case
the use of XML when all you care about is how ANY(array) interacts with the
regular expression operator) and slowly building up until something breaks.
Shooting out a question to the mailing list is probably easier (for you) but
you are more likely to learn and remember if you figure it out yourself.
You had a good beginning with your original e-mail but you really needed to
take it a step or two further. The community is very helpful and forgiving
to these kinds of questions but it is in your own interest to dig deeper
before asking for help. This applies especially for basic and long-present
features such as regular expressions and arrays. And if it really is a bug
you will be able to provide some very helpful information in your report.
David J.
--
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] Implicit transaction not rolling back after error
On Dec 20, 2012, at 3:40 PM, Rob Sargent wrote: > On 12/20/2012 04:33 PM, Stephen Touset wrote: > >> So yes, AUTOCOMMIT is definitely on. > > What does \set show when entered from the psql command line? test=> \set AUTOCOMMIT = 'OFF' *facepalm*. Turns out someone put a .psqlrc with autocommit off in /etc/skel when the box was originally set up as a replacement for our previous app server. Account users were created afterwards, and the change propagated to our application account as well as all of our individual accounts. Why, though, would `SHOW AUTOCOMMIT` lie? And `SET AUTOCOMMIT TO off` says that capability is disabled. So how does the config file manage to do it? Thanks for the insight! -- Stephen Touset Senior Software Engineer stephen.tou...@onelogin.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] Implicit transaction not rolling back after error
On Thu, Dec 20, 2012 at 7:04 PM, Stephen Touset wrote: > On Dec 20, 2012, at 3:40 PM, Rob Sargent wrote: > >> On 12/20/2012 04:33 PM, Stephen Touset wrote: >> >>> So yes, AUTOCOMMIT is definitely on. >> >> What does \set show when entered from the psql command line? > >test=> \set >AUTOCOMMIT = 'OFF' > > *facepalm*. \set is a psql command > Turns out someone put a .psqlrc with autocommit off in /etc/skel when the box > was originally set up as a replacement for our previous app server. Account > users were created afterwards, and the change propagated to our application > account as well as all of our individual accounts. > > Why, though, would `SHOW AUTOCOMMIT` lie? And `SET AUTOCOMMIT TO off` says > that capability is disabled. So how does the config file manage to do it? show variable is a SQL command to the backend engine. The backend does not support autocommit on / off (it did once upon a time for a little while but it broke lots of stuff and got reverted). autocommit is now firmly a client side behavior, not a backend behavior. -- 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 POSIX Regular Expressions on xml type fields gives inconsistent results
"David Johnston" writes: > [optionally go look at " ~* " in the documentation at this point; or just > try a simple flip-flop of the expression] > SELECT 'Kumar' ~* 'kuma' -> TRUE !!! (ah ha! I had the order reversed) > SELECT any( ARRAY['Kumar','Gozales']::text ) ... wait...ANY doesn't work on > the other side... :( > [At this point I'd confirm or question why ANY hasn't been made to go both > ways but also realize that I will have to approach this in a different way > to achieve my goal.] It's been awhile since I looked at the point in detail, but I seem to recall that there are fundamental syntactic-ambiguity reasons why the ANY/ALL part has to be on the righthand side of the comparison operator. There's a much easier fix to this problem though, which is to invent a "reverse ~" operator that does POSIX comparison with the pattern on the left. The hardest part of doing that for yourself is choosing a name for the reverse operator --- it just goes like create function reverse_regexeq(text, text) returns bool as 'select $2 ~ $1' language sql strict immutable; create operator ~~~ (procedure = reverse_regexeq, leftarg = text, rightarg = text); and similarly for the case-insensitive version, and there you go: pattern ~~~ ANY (whatever) solves the problem. Every so often we debate providing built-in operators like this, but we never seem to get past the what-to-call-it part. Anyone have a good color for that bikeshed? 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
