Re: [GENERAL] logging hook
You can log to syslog, and use the syslog definitions file (syslog.conf) to pipe the output to a program that can do whatever you want with it. Susan On Tue, May 13, 2014 at 2:29 PM, Alan Nilsson wrote: > All > > Is there a hook that someone could direct me to that I can use to redirect > logging output? I.e. I would like to write a small module that pre loads > and substitutes the loggers file descriptor with my own to redirect that > output. Possible? > > thanks > 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] Could use some advice on search architecture
First, I would not restrict color to 30 colors, if anything like furniture
or clothing, etc. is involved. Colors are very important to consumers, and
exact colors are important. I would re-think my color selections.
Make sure you have indexes on all the appropriate columns, of course.
Susan
On Fri, Apr 18, 2014 at 6:59 AM, Ron Pasch wrote:
> Hello,
>
> I'm contemplating what architecture I should use to make searching as fast
> as possible given the information available and the search requirements.
> Let me give some background first;
>
> - The database contains products of can potentially have a lot of them (up
> to about 3 to 5 million)
> - Each product has about 30 different properties defined about them.
> Things like what color they are etc. All these properties are enumerated
> choices, so for instance for color there is a list of available static
> never changing options of which one can be chosen for that product. This is
> the same for all those 30 properties. Currently they are stored as
> enumerated types (CREATE TYPE propertyvalue AS ENUM ('option1', 'option2',
> etc..)
> - It should be possible to search for products and provide properties that
> the product SHOULD have, not must have. For instance, for color, the search
> could specify that it should return products that are either red, blue or
> green.
> - The products that match with the most properties should be in the top of
> the search results
> - If different products match with the same amount of properties, the
> ordering should then be on the product that is most popular. There is
> information in the database (and if need be also in the same table) about
> how many times a product is sold.
> - The results will be paginated per 15 products
>
> The requirement is that these searches should be as fast as possible, with
> a maximum of about 200 ms time taken for a search query.
>
> What would be the best approach to this if I were to do this in the
> database only? Should/can this be done with postgresql only or should I
> look into other types of technology? (Lucene? Sphinx? others?)
>
> Any advice on this would be greatly appreciated.
>
> Thx in advance!
>
> Ron
>
>
>
> --
> 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] Re: any way for a transaction to "see" inserts done earlier in the transaction?
I found the problem, and it is all my fault. I was calling the insert function with the wrong combination of parameters, so naturally it didn't find the item. It is working fine now, although I do think I needed to mark the function as VOLATILE, which I think helped. Thanks to all for the help. Susan On Thu, Apr 17, 2014 at 10:33 AM, Vincent Veyron < vincent.vey...@libremen.org> wrote: > On Thu, 17 Apr 2014 10:02:00 -0700 > Susan Cassidy wrote: > > > I moved the code in the function inline into the code, and I still cannot > > find the newly inserted id the next time through the loop. > > I suppose you use DBD::Pg, whose current default isolation transaction > level is ``Serializable'' > > Don't know if it applies, but the Postgresql's documentation says this : > > SERIALIZABLE > > All statements of the current transaction can only see rows committed > before the first query or data-modification statement was executed in this > transaction. If a pattern of reads and writes among concurrent serializable > transactions would create a situation which could not have occurred for any > serial (one-at-a-time) execution of those transactions, one of them will be > rolled back with a serialization_failure SQLSTATE. > > -- > Regards, Vincent Veyron > > http://libremen.com/ > Legal case, contract and insurance claim management software >
Re: [GENERAL] Re: any way for a transaction to "see" inserts done earlier in the transaction?
I don't see how. It is a fairly complicated program, and the perl calls are done through an API, which works fine in all other circumstances (I was told I had to use an API, and not use the Perl calls directly). I moved the code in the function inline into the code, and I still cannot find the newly inserted id the next time through the loop. I think I'm just going to have to commit each time through the loop, although I really hate to. Maybe I can keep a list of the newly inserted rows, and delete them if anything goes wrong later in the loop. Thanks, Susan On Thu, Apr 17, 2014 at 8:13 AM, Scott Marlowe wrote: > So any chance of a self-contained test case so we're not all chasing our > tails? > > On Thu, Apr 17, 2014 at 9:06 AM, Susan Cassidy > wrote: > > Except for the fact that I get the new id returned from the first insert, > > which means that the insert probably did happen. > > > > Susan > > > > > > On Wed, Apr 16, 2014 at 11:55 PM, Alban Hertroys > wrote: > >> > >> On 17 Apr 2014, at 2:49, David G Johnston > >> wrote: > >> > >> > Robert DiFalco wrote > >> >> Two common cases I can think of: > >> >> > >> >> 1. The PERL framework is only caching the insert and does not > actually > >> >> perform it until commit is issued. > >> > > >> > Wouldn't the same mechanism cache the corresponding SELECT? > >> > >> Not likely, or if it did it wouldn't be able to know what id was > returned > >> from the function (which calls nextval(), but that isn't relevant here > since > >> it's marked volatile). > >> That makes it a possible scenario for what's being witnessed here. > >> > >> Alban Hertroys > >> -- > >> If you can't see the forest for the trees, > >> cut the trees and you'll find 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 > > > > > > > > -- > To understand recursion, one must first understand recursion. >
Re: [GENERAL] Re: any way for a transaction to "see" inserts done earlier in the transaction?
Except for the fact that I get the new id returned from the first insert, which means that the insert probably did happen. Susan On Wed, Apr 16, 2014 at 11:55 PM, Alban Hertroys wrote: > On 17 Apr 2014, at 2:49, David G Johnston > wrote: > > > Robert DiFalco wrote > >> Two common cases I can think of: > >> > >> 1. The PERL framework is only caching the insert and does not actually > >> perform it until commit is issued. > > > > Wouldn't the same mechanism cache the corresponding SELECT? > > Not likely, or if it did it wouldn't be able to know what id was returned > from the function (which calls nextval(), but that isn't relevant here > since it's marked volatile). > That makes it a possible scenario for what's being witnessed here. > > Alban Hertroys > -- > If you can't see the forest for the trees, > cut the trees and you'll find 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] Re: any way for a transaction to "see" inserts done earlier in the transaction?
I suppose it's possible. I've never seen this behavior before, but I don't think I've ever used this same scenario before. It is slightly unusual. Susan On Wed, Apr 16, 2014 at 11:55 PM, Alban Hertroys wrote: > On 17 Apr 2014, at 2:49, David G Johnston > wrote: > > > Robert DiFalco wrote > >> Two common cases I can think of: > >> > >> 1. The PERL framework is only caching the insert and does not actually > >> perform it until commit is issued. > > > > Wouldn't the same mechanism cache the corresponding SELECT? > > Not likely, or if it did it wouldn't be able to know what id was returned > from the function (which calls nextval(), but that isn't relevant here > since it's marked volatile). > That makes it a possible scenario for what's being witnessed here. > > Alban Hertroys > -- > If you can't see the forest for the trees, > cut the trees and you'll find 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] any way for a transaction to "see" inserts done earlier in the transaction?
There aren't multiple connections. It is a CGI program. One connection is made when the program starts, and that is all. I've looked at the log. It shows just what I expect, except that the lookup does not work. 0 rows are returned from the select of the newly inserted id. Susan On Wed, Apr 16, 2014 at 7:32 PM, Adrian Klaver wrote: > On 04/16/2014 05:24 PM, Susan Cassidy wrote: > >> I marked it volatile, and still the next time I call the function after >> the first insert, using the previous new id as as input parameter, it >> still can't "find" the newly inserted id for the next go-round. Nor can >> any regular SELECTs in the main program find it. >> > > To expand on my earlier post and to address all the different scenarios > proposed, it might to be a good idea to expand on what you are logging. The > caveat is whether you are working against a production server or a > development. In the development case you would presumably be able to more > easily limit the scope of what you are observing. In that case turning up > the log_statement to 'all' in postgresql.conf would give you a picture of > what is actually hitting the database. So you could see if there where > multiple connections happening or multiple transactions in a connection or > if different tables where being used, etc. That would help create some > anchor points from which you could backwards engineer to possible causes. > > >> Susan >> >> >> > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Re: [GENERAL] any way for a transaction to "see" inserts done earlier in the transaction?
No. One gets done automatically when the transaction fails, however. I can see it in the log. Susan On Wed, Apr 16, 2014 at 6:26 PM, Andy Colson wrote: > On 04/16/2014 07:06 PM, Susan Cassidy wrote: > >> Yes, it is the same connection. It is all the same transaction. >> >> Susan >> >> >> On Wed, Apr 16, 2014 at 5:00 PM, John R Pierce > pie...@hogranch.com>> wrote: >> >> On 4/16/2014 4:53 PM, Susan Cassidy wrote: >> >> Well, it isn't working for me right now. It can't "see" a row >> that was inserted earlier in the transaction. It is a new primary key, and >> when I SELECT it, it isn't found. >> >> >> are you using the same connection ? it won't be visible to a >> different connection until its committed. >> >> -- >> john r pierce 37N 122W >> somewhere on the middle of the left coast >> >> > > Any chance you accidentally fired of a rollback? > > -Andy > >
Re: [GENERAL] Re: any way for a transaction to "see" inserts done earlier in the transaction?
No, I am doing: begin transaction Loop: Do some selects, including id on second iteration of the inserted id Do the insert (function call), which also does a select on an id. Save the newly inserted id for select on the next iteration. This id will be selected by the insert function on the next iteration, sometimes. It is being used the time that it fails. end Loop; do commit if the loop ever successfully gets done. Susan On Wed, Apr 16, 2014 at 5:49 PM, David G Johnston < david.g.johns...@gmail.com> wrote: > I'm presuming the OP is using the typical model of: > conn = getConnection() > id = doInsert(conn) > rst = doSelect(conn, id) > doSomething(rst) > conn.commit() > conn.relrease() > > > Robert DiFalco wrote > > Two common cases I can think of: > > > > 1. The PERL framework is only caching the insert and does not actually > > perform it until commit is issued. > > Wouldn't the same mechanism cache the corresponding SELECT? > > > > 2. You really are not on the same transaction even though it appears you > > are and the transaction isolation is such that you cannot see the insert > > until it is fully committed. > > Doubtful given the way most programs are coded (see assumption above) - the > SELECT should be able to see the prior statement results whether committed > or not. > > The only thing I can think of on this line-of-though is that auto-commit is > off and while the original INSERT succeeded the transaction it was in was > not "COMMIT"ed and the connection used closed/returned-to-pool with an > implicit ROLLBACK. Thus when the subsequent SELECT occurred the INSERT > never appeared to happen. > > Not knowing the whether the ID is visible post-program-completion limits > the > ability to diagnose, though. > > David J. > > > > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/any-way-for-a-transaction-to-see-inserts-done-earlier-in-the-transaction-tp5800432p5800466.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] Re: any way for a transaction to "see" inserts done earlier in the transaction?
It is never committed, because the lookup for the insert fails. Susan On Wed, Apr 16, 2014 at 5:39 PM, David G Johnston < david.g.johns...@gmail.com> wrote: > Susan Cassidy-3 wrote > > Nor can any regular SELECTs in the main program find it. > > Ever? > > If this is a same transaction visibility issue then when your Perl program > stops you should be able to go find that ID manually to confirm it was > inserted and committed properly. If you still cannot find the ID then this > whole line of exploration (i.e., same session visibility) is pointless > since > we know beyond doubt committed data is visible to all other sessions. > > This would also further support the mistaken object identity theory I > proposed up-thread. > > David J. > > > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/any-way-for-a-transaction-to-see-inserts-done-earlier-in-the-transaction-tp5800432p5800463.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] Re: any way for a transaction to "see" inserts done earlier in the transaction?
I've never had that happen before, and I've used Perl and DBI a lot. Susan On Wed, Apr 16, 2014 at 5:34 PM, Robert DiFalco wrote: > Two common cases I can think of: > > 1. The PERL framework is only caching the insert and does not actually > perform it until commit is issued. > 2. You really are not on the same transaction even though it appears you > are and the transaction isolation is such that you cannot see the insert > until it is fully committed. > > > On Wed, Apr 16, 2014 at 5:28 PM, David G Johnston < > david.g.johns...@gmail.com> wrote: > >> One possibility is that the INSERT is going to a different table (having >> the >> same name but existing in a different schema) that is visible/default to >> the >> function but not outside of it. >> >> Or the function on the server is not "current" and thus isn't doing what >> you >> think it is. >> >> >> > I do an insert via a function, which returns the new id, then later I >> try >> > to SELECT on that id, and it doesn't find it. >> > >> > Could it be because the insert is done inside a function? >> >> Not by itself; but that factor could be interacting with something else to >> cause the observed behavior. As noted above functions are able to >> maintain >> their own "schema" environment so what is executed in one and outside of >> one >> can indeed target different physical objects - which has nothing to do >> with >> transaction visibility. >> >> >> Susan Cassidy-3 wrote >> > It is a fairly large and complex Perl program, so no, not really. >> >> Then you need to recreate a functionally similar, but limited, test case >> that either exhibits the behavior in question or causes you to realize >> what >> you are doing in wrong in the "large and complex Perl program". >> >> David J. >> >> >> >> >> -- >> View this message in context: >> http://postgresql.1045698.n5.nabble.com/any-way-for-a-transaction-to-see-inserts-done-earlier-in-the-transaction-tp5800432p5800459.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] any way for a transaction to "see" inserts done earlier in the transaction?
I marked it volatile, and still the next time I call the function after the first insert, using the previous new id as as input parameter, it still can't "find" the newly inserted id for the next go-round. Nor can any regular SELECTs in the main program find it. Susan On Wed, Apr 16, 2014 at 5:18 PM, Susan Cassidy < susan.cass...@decisionsciencescorp.com> wrote: > It isn't marked as one of those as all, so whatever the default is. > > That could be it. I'll look up the default. > > Thanks, > Susan > > > On Wed, Apr 16, 2014 at 5:17 PM, Tom Lane wrote: > >> Susan Cassidy writes: >> > It is a fairly large and complex Perl program, so no, not really. >> > I do an insert via a function, which returns the new id, then later I >> try >> > to SELECT on that id, and it doesn't find it. >> >> > Could it be because the insert is done inside a function? >> >> Is the SELECT also inside a database function, and if so is that function >> marked stable or immutable? That might explain it --- non-volatile >> functions are intentionally designed not to notice updates that happen >> after they start. >> >> regards, tom lane >> > >
Re: [GENERAL] any way for a transaction to "see" inserts done earlier in the transaction?
It isn't marked as one of those as all, so whatever the default is. That could be it. I'll look up the default. Thanks, Susan On Wed, Apr 16, 2014 at 5:17 PM, Tom Lane wrote: > Susan Cassidy writes: > > It is a fairly large and complex Perl program, so no, not really. > > I do an insert via a function, which returns the new id, then later I try > > to SELECT on that id, and it doesn't find it. > > > Could it be because the insert is done inside a function? > > Is the SELECT also inside a database function, and if so is that function > marked stable or immutable? That might explain it --- non-volatile > functions are intentionally designed not to notice updates that happen > after they start. > > regards, tom lane >
Re: [GENERAL] any way for a transaction to "see" inserts done earlier in the transaction?
It seems to be returning the right id. It should be next for the serial datatype. Susan On Wed, Apr 16, 2014 at 5:13 PM, Bosco Rama wrote: > On 04/16/14 17:08, Susan Cassidy wrote: > > The function does a select to see if the id number exists, and it fails. > > NOT FOUND causes a RAISE EXCEPTION. > > Is it returning the right id? I seem to remember a recent thread > about Perl DBI returning the wrong id's for certain operations. > > Just at thought. > > Bosco. >
Re: [GENERAL] any way for a transaction to "see" inserts done earlier in the transaction?
The function does a select to see if the id number exists, and it fails. NOT FOUND causes a RAISE EXCEPTION. Susan On Wed, Apr 16, 2014 at 5:05 PM, Susan Cassidy < susan.cass...@decisionsciencescorp.com> wrote: > It is a fairly large and complex Perl program, so no, not really. > > I do an insert via a function, which returns the new id, then later I try > to SELECT on that id, and it doesn't find it. > > Could it be because the insert is done inside a function? > > Susan > > > On Wed, Apr 16, 2014 at 4:58 PM, Steven Schlansker wrote: > >> >> >> >> On Wed, Apr 16, 2014 at 4:31 PM, Tom Lane wrote: >> >> Susan Cassidy writes: >> >> > Is there any way to let a transaction "see" the inserts that were >> done >> >> > earlier in the transaction? >> >> >> >> It works that way automatically, as long as you're talking about >> separate >> >> statements within one transaction. >> >> >> >> regards, tom lane >> >> > On Apr 16, 2014, at 4:53 PM, Susan Cassidy < >> susan.cass...@decisionsciencescorp.com> wrote: >> > Well, it isn't working for me right now. It can't "see" a row that was >> inserted earlier in the transaction. It is a new primary key, and when I >> SELECT it, it isn't found. >> > >> >> Can you share the code that does not work with us? Preferably as a small >> self-contained example. >> >> >
Re: [GENERAL] any way for a transaction to "see" inserts done earlier in the transaction?
It is a fairly large and complex Perl program, so no, not really. I do an insert via a function, which returns the new id, then later I try to SELECT on that id, and it doesn't find it. Could it be because the insert is done inside a function? Susan On Wed, Apr 16, 2014 at 4:58 PM, Steven Schlansker wrote: > > > >> On Wed, Apr 16, 2014 at 4:31 PM, Tom Lane wrote: > >> Susan Cassidy writes: > >> > Is there any way to let a transaction "see" the inserts that were done > >> > earlier in the transaction? > >> > >> It works that way automatically, as long as you're talking about > separate > >> statements within one transaction. > >> > >> regards, tom lane > > > On Apr 16, 2014, at 4:53 PM, Susan Cassidy < > susan.cass...@decisionsciencescorp.com> wrote: > > Well, it isn't working for me right now. It can't "see" a row that was > inserted earlier in the transaction. It is a new primary key, and when I > SELECT it, it isn't found. > > > > Can you share the code that does not work with us? Preferably as a small > self-contained example. > >
Re: [GENERAL] any way for a transaction to "see" inserts done earlier in the transaction?
Yes, it is the same connection. It is all the same transaction. Susan On Wed, Apr 16, 2014 at 5:00 PM, John R Pierce wrote: > On 4/16/2014 4:53 PM, Susan Cassidy wrote: > >> Well, it isn't working for me right now. It can't "see" a row that was >> inserted earlier in the transaction. It is a new primary key, and when I >> SELECT it, it isn't found. >> > > are you using the same connection ? it won't be visible to a different > connection until its committed. > > -- > 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] any way for a transaction to "see" inserts done earlier in the transaction?
Well, it isn't working for me right now. It can't "see" a row that was inserted earlier in the transaction. It is a new primary key, and when I SELECT it, it isn't found. Susan On Wed, Apr 16, 2014 at 4:31 PM, Tom Lane wrote: > Susan Cassidy writes: > > Is there any way to let a transaction "see" the inserts that were done > > earlier in the transaction? > > It works that way automatically, as long as you're talking about separate > statements within one transaction. > > regards, tom lane >
[GENERAL] any way for a transaction to "see" inserts done earlier in the transaction?
Is there any way to let a transaction "see" the inserts that were done earlier in the transaction? I want to insert a row, then later use it within the same transaction. If not, I will have to commit after each insert, and I don't want to do that until add the rows are added, if I can possibly avoid it. Thanks, Susan
[GENERAL] Problem with query
I have a query with several joins, where I am searching for specific data in certain columns. If I do this: SELECT distinct on (s.description, st1.description, s.scene_id) s.description, s.scene_id, to_char(s.time_of_creation, 'MM/DD/YY HH24:MI:SS'), position_0_0_0_info, st.scene_thing_id, si.description, st.description, m.description from scenes s left outer join scene_thing_instances si on s.scene_id = si.scene_id left outer join scene_things st on si.scene_thing_id = st.scene_thing_id left outer join materials m on st.material_id = m.material_id left outer join scene_things st1 on st.ultimate_parent_id = st1.ultimate_parent_id where st.description ilike '%bread%' or st1.description ilike '%bread%' group by s.description, st1.description, s.scene_id, st.scene_thing_id, si.description, m.description order by s.description No results are found, but if I just do this: SELECT st.description, st1.description from scene_things st left outer join scene_things st1 on st.ultimate_parent_id = st1.ultimate_parent_id where st1.description ilike '%bread%' group by st.description, st1.description order by st.description I get the results I expect (several hits). What is the first query doing wrong? I've tried adding st1.description to the SELECT list, and the GROUP BY clause, with no luck. Thanks, Susan
Re: [GENERAL] puzzling perl DBI vs psql problem
The only one that comes out different is inet_server_addr, via the program, it comes out: ::1 whereas via psql it comes out empty. Yes, I am 100% sure I am using the same schema (which I never specify, so I am using 'public') and the same user and database. Susan On Thu, Mar 13, 2014 at 1:53 PM, Dennis Jenkins wrote: > 1) > Run both "psql" and "perl" under "strace" and search the output for which > sockets it connects to. > eg, strace -o /tmp/psql.log psql -Upgsql -dmydatabase -c"select > version();" > > > 2) > Add a query into your perl script to perform the following SQL and print > the results: > > select current_database(); > select current_schema(); > select inet_server_addr(); > select current_user; > (and others, see > http://www.postgresql.org/docs/9.3/static/functions-info.html for more > functions) > Do the same from your "psql". Compare the output. Are you 110% sure that > you are connecting to the same database, as the same user, and using the > same schema? > > > > > On Thu, Mar 13, 2014 at 3:44 PM, Susan Cassidy < > susan.cass...@decisionsciencescorp.com> wrote: > >> No, I don't have 2 instances running. I default the port on the psql >> command line, and the perl program is using 5432, as normal. >> >> Now, I'm discovering that syslog is no longer logging anything. I >> bounced it, but to no avail. >> >> Susan >> >> >> On Thu, Mar 13, 2014 at 1:34 PM, Rodrigo Gonzalez < >> rjgonzale.li...@gmail.com> wrote: >> >>> On Thu, 13 Mar 2014 13:28:38 -0700 >>> Susan Cassidy wrote: >>> >>> > No, it is connecting to localhost, which is the same system I am >>> > running psql on. >>> > >>> > Susan >>> > >>> Well, if one query is logged and the other one is not it means that it >>> is running against different servers (as far as I understand >>> logging) >>> >>> Maybe psql is connecting using one socket and perl using another one? >>> maybe you have 2 instances running? >>> >> >> >
Re: [GENERAL] puzzling perl DBI vs psql problem
I finally figured it out, after changing my code to output the lines per the number of rows of output, instead of until data[0] was blank. It turned out that data[0] was sometimes blank, and I forgot about that, and was stopping the output after I got back an empty record (or so I thought). So, all my fault. The syslog thing I fixed by changing log_min_duration to 0, instead of letting it default. I don't think this used to be the default (to not log any statements). Thanks for all the ideas, anyway, folks. Thanks again, Susan On Thu, Mar 13, 2014 at 2:27 PM, Rob Sargent wrote: > On 03/13/2014 03:23 PM, Susan Cassidy wrote: > >> The only one that comes out different is inet_server_addr, via the >> program, it comes out: >> ::1 >> whereas via psql it comes out empty. >> >> Yes, I am 100% sure I am using the same schema (which I never specify, so >> I am using 'public') and the same user and database. >> >> Susan >> >> >> >> Unless username is also a schema name, then you're in that schema. > > > -- > 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] puzzling perl DBI vs psql problem
70c70 < scassidy 5386 2.3 4.0 1319732 325320 ? Sl Feb20 732:01 /usr/lib64/firefox/plugin-container /usr/lib64/flash-plugin/libflashplayer.so -greomni /usr/lib64/firefox/omni.ja -appomni /usr/lib64/firefox/browser/omni.ja -appdir /usr/lib64/firefox/browser 5296 plugin --- > scassidy 5386 2.3 4.0 1319732 325360 ? Sl Feb20 732:01 /usr/lib64/firefox/plugin-container /usr/lib64/flash-plugin/libflashplayer.so -greomni /usr/lib64/firefox/omni.ja -appomni /usr/lib64/firefox/browser/omni.ja -appdir /usr/lib64/firefox/browser 5296 plugin 92,93c92,93 < apache 24831 0.0 0.0 186452 2864 ?S13:46 0:00 /usr/sbin/httpd -k start < apache 24832 0.0 0.0 186452 3436 ?S13:46 0:00 /usr/sbin/httpd -k start --- > apache 24831 0.0 0.0 186452 3380 ?S13:46 0:00 /usr/sbin/httpd -k start > apache 24832 0.0 0.0 186452 3440 ?S13:46 0:00 /usr/sbin/httpd -k start 96,99c96,99 < apache 24835 0.0 0.0 186452 3424 ?S13:46 0:00 /usr/sbin/httpd -k start < apache 24836 0.0 0.0 186452 3376 ?S13:46 0:00 /usr/sbin/httpd -k start < apache 24837 0.0 0.0 186452 3376 ?S13:46 0:00 /usr/sbin/httpd -k start < apache 24838 0.0 0.0 186452 3404 ?S13:46 0:00 /usr/sbin/httpd -k start --- > apache 24835 0.0 0.0 186452 3428 ?S13:46 0:00 /usr/sbin/httpd -k start > apache 24836 0.0 0.0 186452 3440 ?S13:46 0:00 /usr/sbin/httpd -k start > apache 24837 0.0 0.0 186452 3380 ?S13:46 0:00 /usr/sbin/httpd -k start > apache 24838 0.0 0.0 186452 3424 ?S13:46 0:00 /usr/sbin/httpd -k start 101,102c101,103 < scassidy 25341 0.0 0.0 121136 1264 pts/2R+ 14:25 0:00 ps auxww < scassidy 25342 0.0 0.0 103256 848 pts/2S+ 14:25 0:00 grep p --- > apache 25355 1.2 0.1 99808 13400 ?S14:25 0:00 /usr/bin/perl /var/www/cgi-bin/examplepgm.pl > postgres 25356 0.0 0.0 129240 5440 ?Ss 14:25 0:00 postgres: testuser scan_run_db ::1(42097) idle > scassidy 25358 0.0 0.0 121128 1252 pts/2R+ 14:25 0:00 ps auxww 103a105 > scassidy 25360 0.0 0.0 103256 856 pts/2S+ 14:25 0:00 grep p Susan On Thu, Mar 13, 2014 at 1:55 PM, Rob Sargent wrote: > On 03/13/2014 02:44 PM, Susan Cassidy wrote: > > No, I don't have 2 instances running. I default the port on the psql > command line, and the perl program is using 5432, as normal. > > Now, I'm discovering that syslog is no longer logging anything. I bounced > it, but to no avail. > > Susan > > > On Thu, Mar 13, 2014 at 1:34 PM, Rodrigo Gonzalez < > rjgonzale.li...@gmail.com> wrote: > >> On Thu, 13 Mar 2014 13:28:38 -0700 >> Susan Cassidy wrote: >> >> > No, it is connecting to localhost, which is the same system I am >> > running psql on. >> > >> > Susan >> > >> Well, if one query is logged and the other one is not it means that it >> is running against different servers (as far as I understand >> logging) >> >> Maybe psql is connecting using one socket and perl using another one? >> maybe you have 2 instances running? >> > > I would like to see the output of > > ps auxw | grep p > > when each or both is running > >
Re: [GENERAL] puzzling perl DBI vs psql problem
No, I don't have 2 instances running. I default the port on the psql command line, and the perl program is using 5432, as normal. Now, I'm discovering that syslog is no longer logging anything. I bounced it, but to no avail. Susan On Thu, Mar 13, 2014 at 1:34 PM, Rodrigo Gonzalez wrote: > On Thu, 13 Mar 2014 13:28:38 -0700 > Susan Cassidy wrote: > > > No, it is connecting to localhost, which is the same system I am > > running psql on. > > > > Susan > > > Well, if one query is logged and the other one is not it means that it > is running against different servers (as far as I understand > logging) > > Maybe psql is connecting using one socket and perl using another one? > maybe you have 2 instances running? >
Re: [GENERAL] puzzling perl DBI vs psql problem
No, it is connecting to localhost, which is the same system I am running psql on. Susan On Thu, Mar 13, 2014 at 1:26 PM, Rodrigo Gonzalez wrote: > On Thu, 13 Mar 2014 13:20:53 -0700 > Susan Cassidy wrote: > > > Another odd thing is that it is apparently not logging statements from > > Perl, only from psql. I don't know why. I thought I had it set up > > right to log to syslog. I've had good luck before with that on other > > installations. > > > > I can bet that it is connecting to another database or server, check > your perl script configuration. > > Best regards > > Rodrigo Gonzalez >
Re: [GENERAL] puzzling perl DBI vs psql problem
No, I am connecting to the right database, as my log info shows. No duplicate table names, except in different databases. Those tables show all the data I expect, oddly enough, if I connect the perl program to that database. It is only this database that is giving me trouble. Which is another oddity. The identical test database is working fine. Only this database is giving me trouble. Naturally the "live" database is causing problems. Susan On Thu, Mar 13, 2014 at 12:25 PM, Tom Lane wrote: > Susan Cassidy writes: > > I have a weird issue that I can't figure out. > > If I run the exact same query through psql as through perl DBI, I get > > different results. I get far fewer results with DBI than through the > psql > > command line. > > Any possibility that the perl program is connecting to a different > database, with similar-but-not-identical data in it? > > I've seen variants on that theme involving different users with different > search_path settings finding different-but-similarly-named tables in > different schemas of the same database. > > regards, tom lane >
Re: [GENERAL] puzzling perl DBI vs psql problem
Yes, I am running with use strict. The statement I pasted in is after perl quoting, being written out by the same perl program. I just take that statement and paste it into the psql window. DBI->trace showed nothing out of the ordinary. It just shows the lines being fetched that I am seeing in the web program, not the lines I get from psql. Another odd thing is that it is apparently not logging statements from Perl, only from psql. I don't know why. I thought I had it set up right to log to syslog. I've had good luck before with that on other installations. Here are the log settings in postgresql.conf: log_destination = 'syslog' # Valid values are combinations of # stderr, csvlog, syslog, and eventlog, # depending on platform. csvlog # requires logging_collector to be on. # This is used when logging to stderr: logging_collector = on # Enable capturing of stderr and csvlog #logging_collector = off# Enable capturing of stderr and csvlog # into log files. Required to be on for # csvlogs. # These are only used if logging_collector is on: log_directory = 'pg_log'# directory where log files are written, log_filename = 'postgresql-%a.log' # log file name pattern, #log_file_mode = 0600 # creation mode for log files, log_truncate_on_rotation = on # If on, an existing log file with the # same name as the new log file will be log_rotation_age = 1d # Automatic rotation of logfiles will log_rotation_size = 0 # Automatic rotation of logfiles will # happen after that much log output. # These are relevant when logging to syslog: syslog_facility = 'LOCAL0' syslog_ident = 'postgres' # This is only relevant when logging to eventlog (win32): client_min_messages = log # values in order of decreasing detail: # log log_min_messages = info # values in order of decreasing detail: #log_min_messages = warning # values in order of decreasing detail: # log #log_min_error_statement = error# values in order of decreasing detail: # log #log_min_duration_statement = -1# -1 is disabled, 0 logs all statements # and their durations, > 0 logs only #log_checkpoints = off #log_connections = off #log_disconnections = off #log_duration = off #log_error_verbosity = default # terse, default, or verbose messages #log_hostname = off log_line_prefix = '%d %u %p %t '# special values: Could it have something to do with permissions on /var/log/postgresql? It is writeable by root only. The perl program runs under apache. Susan On Thu, Mar 13, 2014 at 12:46 PM, Steve Atkins wrote: > > On Mar 13, 2014, at 12:18 PM, Susan Cassidy < > susan.cass...@decisionsciencescorp.com> wrote: > > > I have a weird issue that I can't figure out. > > > > If I run the exact same query through psql as through perl DBI, I get > different results. I get far fewer results with DBI than through the psql > command line. > > > > Any ideas why that would be? > > > > The query is: > > SELECT st.description, st.scene_thing_instance_id, > > st.scene_id, sc.description, st.scene_thing_id, s.description, > > st.position_x, st.position_y, st.position_z, > >CASE > > when (st.description = 'absolute root'::text) then 1 > > when (st.description ilike 'root%') then 2 > > else 3 > >END as s1, s.shape_name_id, sn.shape_name > > from scene_thing_instances st > > left join scene_things s on st.scene_thing_id = s.scene_thing_id > > left join scenes sc on st.scene_id = sc.scene_id > > left outer join shape_names sn on s.shape_name_id = > sn.shape_name_id > > > > order by s1, st.description > > > > I get 14 rows back via psql, but I only get 5 rows back via DBI. It's > very puzzling. > > > > I copied and pasted the query from the program's log file, so I know I'm > doing the exact same query. If it matters, I'm only seeing the rows with > 'root' in them via DBI, which the CASE statement refers to. > > How are you quoting the string in perl, and are you running with use > strict? > &
[GENERAL] puzzling perl DBI vs psql problem
I have a weird issue that I can't figure out. If I run the exact same query through psql as through perl DBI, I get different results. I get far fewer results with DBI than through the psql command line. Any ideas why that would be? The query is: SELECT st.description, st.scene_thing_instance_id, st.scene_id, sc.description, st.scene_thing_id, s.description, st.position_x, st.position_y, st.position_z, CASE when (st.description = 'absolute root'::text) then 1 when (st.description ilike 'root%') then 2 else 3 END as s1, s.shape_name_id, sn.shape_name from scene_thing_instances st left join scene_things s on st.scene_thing_id = s.scene_thing_id left join scenes sc on st.scene_id = sc.scene_id left outer join shape_names sn on s.shape_name_id = sn.shape_name_id order by s1, st.description I get 14 rows back via psql, but I only get 5 rows back via DBI. It's very puzzling. I copied and pasted the query from the program's log file, so I know I'm doing the exact same query. If it matters, I'm only seeing the rows with 'root' in them via DBI, which the CASE statement refers to. Thanks, Susan
Re: [GENERAL] another trigger problem
Bingo, this COPY file did not have the datetimeval, so I added a few lines of code to convert it from the ctime-type entry that exists in the record. You would think that postgres could have output a more helpful error message, though. Thanks a lot for the assist. Susan On Fri, Mar 7, 2014 at 3:18 PM, Rob Sargent wrote: > On 03/07/2014 04:06 PM, Adrian Klaver wrote: > >> On 03/07/2014 02:48 PM, Susan Cassidy wrote: >> >>> I have another problem with a slightly different trigger. It's very >>> weird, because it is exactly the same as the first trigger, that now >>> works, except for the table name. >>> >>> The error is: >>> >>> ERROR: query string argument of EXECUTE is null >>> CONTEXT: PL/pgSQL function metric_int_insert_func() line 5 at EXECUTE >>> statement >>> >>> >>> The trigger is: >>> >>> CREATE OR REPLACE FUNCTION metric_int_insert_func() >>> RETURNS TRIGGER AS $$ >>>DECLARE insert_sql text; >>> BEGIN >>> insert_sql:='insert into metric_int_values_' || >>> to_char(NEW.datetimeval,'MM') || ' values ($1.*)'; >>> EXECUTE insert_sql using NEW; >>> RETURN NULL; >>> END; >>> $$ >>> LANGUAGE plpgsql; >>> >>> DROP TRIGGER IF EXISTS insert_metric_int_insert_trigger on >>> metric_int_values; >>> CREATE TRIGGER insert_metric_int_insert_trigger >>> BEFORE INSERT ON metric_int_values >>> FOR EACH ROW EXECUTE PROCEDURE metric_int_insert_func(); >>> >>> >>> which is exactly the same as this one that works: >>> CREATE OR REPLACE FUNCTION metric_double_insert_func() >>> RETURNS TRIGGER AS $$ >>>DECLARE insert_sql text; >>> BEGIN >>> insert_sql:='insert into metric_double_values_' || >>> to_char(NEW.datetimeval,'MM') || ' values ($1.*)'; >>> EXECUTE insert_sql using NEW; >>> RETURN NULL; >>> END; >>> $$ >>> LANGUAGE plpgsql; >>> >>> DROP TRIGGER IF EXISTS insert_metric_double_insert_trigger on >>> metric_double_values; >>> CREATE TRIGGER insert_metric_double_insert_trigger >>> BEFORE INSERT ON metric_double_values >>> FOR EACH ROW EXECUTE PROCEDURE metric_double_insert_func(); >>> >>> >>> I can't seem to figure it out. I've retyped some of the lines, in case >>> there is a weird character somewhere, but they got there with a vi yank >>> and put, so that's not likely. >>> >>> Anyone have any ideas? >>> >> >> Try dropping the function and then creating it, instead of just the >> create and replace. I have seen issues in the past with a stale copy of a >> function causing a problem. >> >> >>> Thanks, >>> Susan >>> >>> >>> Is the datetimeval always non-null? > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
[GENERAL] another trigger problem
I have another problem with a slightly different trigger. It's very weird, because it is exactly the same as the first trigger, that now works, except for the table name. The error is: ERROR: query string argument of EXECUTE is null CONTEXT: PL/pgSQL function metric_int_insert_func() line 5 at EXECUTE statement The trigger is: CREATE OR REPLACE FUNCTION metric_int_insert_func() RETURNS TRIGGER AS $$ DECLARE insert_sql text; BEGIN insert_sql:='insert into metric_int_values_' || to_char(NEW.datetimeval,'MM') || ' values ($1.*)'; EXECUTE insert_sql using NEW; RETURN NULL; END; $$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS insert_metric_int_insert_trigger on metric_int_values; CREATE TRIGGER insert_metric_int_insert_trigger BEFORE INSERT ON metric_int_values FOR EACH ROW EXECUTE PROCEDURE metric_int_insert_func(); which is exactly the same as this one that works: CREATE OR REPLACE FUNCTION metric_double_insert_func() RETURNS TRIGGER AS $$ DECLARE insert_sql text; BEGIN insert_sql:='insert into metric_double_values_' || to_char(NEW.datetimeval,'MM') || ' values ($1.*)'; EXECUTE insert_sql using NEW; RETURN NULL; END; $$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS insert_metric_double_insert_trigger on metric_double_values; CREATE TRIGGER insert_metric_double_insert_trigger BEFORE INSERT ON metric_double_values FOR EACH ROW EXECUTE PROCEDURE metric_double_insert_func(); I can't seem to figure it out. I've retyped some of the lines, in case there is a weird character somewhere, but they got there with a vi yank and put, so that's not likely. Anyone have any ideas? Thanks, Susan
[GENERAL] problem with trigger function
I'm having a problem with a trigger function. I've been googling for over an hour, with no luck with my specific problem. I get this error: ERROR: missing FROM-clause entry for table "new" LINE 1: insert into metric_double_values_201203 values (NEW.metricID... ^ QUERY: insert into metric_double_values_201203 values (NEW.metricID, NEW.sourceID, NEW.timestamp, NEW.value, NEW.datetimeval) CONTEXT: PL/pgSQL function metric_double_insert_func() line 8 at EXECUTE statement >From this trigger function: CREATE OR REPLACE FUNCTION metric_double_insert_func() RETURNS TRIGGER AS $$ DECLARE insert_sql text; BEGIN insert_sql:='insert into metric_double_values_' || to_char(NEW.datetimeval,'MM') || ' values (NEW.metricID, NEW.sourceID, NEW.timestamp, NEW.value, NEW.datetimeval)'; EXECUTE insert_sql using NEW; RETURN NULL; END; $$ LANGUAGE plpgsql; DROP TRIGGER insert_metric_double_insert_trigger on metric_double_values; CREATE TRIGGER insert_metric_double_insert_trigger BEFORE INSERT ON metric_double_values FOR EACH ROW EXECUTE PROCEDURE metric_double_insert_func(); This was an attempt at eliminating the error I got when trying to insert with values (NEW.*) using NEW: ERROR: missing FROM-clause entry for table "new" LINE 1: insert into metric_double_values_201203 values (NEW.*) ^ QUERY: insert into metric_double_values_201203 values (NEW.*) CONTEXT: PL/pgSQL function metric_double_insert_func() line 7 at EXECUTE statement I don't know what from clause it is talking about This is a trigger for inserting rows into the proper partition table based on date. Any help appreciated. Thanks, Susan
[GENERAL] why does documentation use "END CASE" when that doesn't work
The documentation says: CASE search-expression WHEN expression [, expression [ ... ]] THEN statements [ WHEN expression [, expression [ ... ]] THEN statements ... ] [ ELSE statements ] END CASE; And it repeats the "END CASE" phrase in other examples. However, that doesn't work. You have to just use "END". Shouldn't the documentation be fixed? Susan
Re: [GENERAL] problem with query and group by error
I tried distinct on srs.scan_run_id, which is a primary key, and got an error, but I tried it again just now, and it worked fine. Thanks for having me try it again. I had ended up with group by srs.scan_run_id, sty.scan_type, ssn.scan_system_name, ssn.simulation, ssitenames.scan_site_name, ssitenames.scan_site_name, ssitenicknames.scan_site_nickname, hvhi.hardware_version_or_hardware_identifier_name, sv.software_version, sc.description because I kept adding column names that I got errors on, but this is by no means the whole list. So, I'm still confused as to why I got the error in the first place. Someone said something about "Yes, except those that are inside the aggregate." but I don't have an aggregate specified. Susan On Fri, Feb 21, 2014 at 10:38 AM, Scott Marlowe wrote: > On Fri, Feb 21, 2014 at 10:40 AM, Susan Cassidy > wrote: > > > > I originally had the query without the group by, but I had duplicate > rows, > > so I added a group by to eliminate them. > > > Have you tried select distinct or select distinct on ()? >
[GENERAL] problem with query and group by error
I have a large query: SELECT distinct on (srs.scan_run_id) srs.scan_run_id, srs.run_request_number, srs.container_id, srs.manifest_id, srs.scan_system_name_id, srs.scan_site_name_id, srs.scan_site_nickname_id, to_char(srs.start_time, 'MM/DD/YY HH24:MI:SS'), to_char(srs.stop_time, 'MM/DD/YY HH24:MI:SS'), srs.system_operator, srs.system_baseline_configuration_file_version_id, srs.container_contents, srs.container_run_truth_data, srs.scan_type_id, sty.scan_type, srs.hardware_version_or_hardware_identifier_id, srs.software_version_id, srs.operator_notes, to_char(srs.expiration_date, 'MM/DD/YY HH24:MI:SS'), srs.scan_outcome_id, to_char(srs.alarm_time, 'MM/DD/YY HH24:MI:SS'), srs.alarm_decision_id, srs.material_detected_id, srs.data_access, ssn.scan_system_name, ssn.simulation, ssitenames.scan_site_name, ssitenicknames.scan_site_nickname, hvhi.hardware_version_or_hardware_identifier_name, sv.software_version, sc.description from scan_run_summary srs left outer join scan_system_names ssn on srs.scan_system_name_id = ssn.scan_system_name_id left outer join scan_site_names ssitenames on srs.scan_site_name_id = ssitenames.scan_site_name_id left outer join scan_site_nicknames ssitenicknames on srs.scan_site_nickname_id = ssitenicknames.scan_site_nickname_id left outer join hardware_version_or_hardware_identifiers hvhi on srs.hardware_version_or_hardware_identifier_id = hvhi.hardware_version_or_hardware_identifier_id left outer join software_versions sv on srs.software_version_id = sv.software_version_id left outer join scenes sc on srs.container_run_truth_data = sc.scene_id left outer join scan_types sty on srs.scan_type_id = sty.scan_type_id join scene_thing_instances sti on srs.container_run_truth_data = sti.scene_id join scene_things stg on sti.scene_thing_id = stg.scene_thing_id group by srs.scan_run_id ; That gives this error: ERROR: column "sty.scan_type" must appear in the GROUP BY clause or be used in an aggregate function LINE 5:srs.scan_type_id, sty.scan_type, srs.hardware_version... I don't see why sty.scan_type should be singled out as requiring a group by clause, when there are many other columns specified. If I add scan_type to the group by, then it gives the same error, but with ssn.scan_system_name. Am I going to have to specify all the columns in the group by clause? I originally had the query without the group by, but I had duplicate rows, so I added a group by to eliminate them. Thanks, Susan
Re: [GENERAL] way to custom sort column by fixed strings, then by field's content
Works great, thanks a bunch. Susan On Mon, Feb 3, 2014 at 12:39 PM, Rob Sargent wrote: > On 02/03/2014 01:01 PM, Susan Cassidy wrote: > > description| description > ---+- > 18 cm long wrench | Scene 1 > absolute root | > blue screwdriver | Scene 1 > red toolbox | Scene 1 > root 3| Scene 1 > root 4| Scene 2 > root 6| Scene 3 > small wrench | Scene 1 > tire | Scene 2 > > > > On Mon, Feb 3, 2014 at 11:14 AM, Adrian Klaver wrote: > >> On 02/03/2014 10:53 AM, Susan Cassidy wrote: >> >>> I have a column that contains items like >>> 'absolute root' >>> 'root 3' >>> 'root 4' >>> 'root 5' >>> 'scene 1' >>> 'scene 2' >>> 'scene 3' >>> >>> and I would like them to sort in that order. >>> >>> I tried: >>> select sti.description, sc.description from scene_thing_instances sti >>> join scenes sc on sti.scene_id = sc.scene_id >>>order by CASE sc.description >>> when (sc.description = 'absolute root'::text) then 1 >>> when (sc.description ilike 'root%') then 2 >>> else 3 >>> END; >>> >>> I was starting with this, and was going to add perhaps another case >>> statement. >>> >>> But it gives me: >>> ERROR: operator does not exist: text = boolean >>> LINE 3: when (sc.description = 'absolute root'::text) th... >>> ^ >>> HINT: No operator matches the given name and argument type(s). You >>> might need to add explicit type casts. >>> >>> I don't understand this because description is a text column, not >>> boolean, and certainly 'absolute root'::text is a text string. >>> >>> This is 9.2. >>> >>> Ideas, anyone? >>> >> >> select sti.description, sc.description from scene_thing_instances sti >> join scenes sc on sti.scene_id = sc.scene_id >> order by sc.description, CASE >> when (sc.description = 'absolute root'::text) then 1 >> when (sc.description ilike 'root%') then 2 >> else 3 >>END; >> >> >>> Thanks, >>> Susan >>> >> >> >> -- >> Adrian Klaver >> adrian.kla...@gmail.com >> > > Sorry this is what I meant to post > > select s.s1, s.s2, (CASE > when (s.s1 = 'absolute root'::text) then 1 > when (s.s1 ~* '^root*') then 2 > else 3 >END) as v > from scripts as s > order by v,s1 > > toys-# ; > s1 | s2| v > ---+-+--- > absolute root | | 1 > root 3| Scene 1 | 2 > root 4| Scene 2 | 2 > root 6| Scene 3 | 2 > 18 cm long wrench | Scene 1 | 3 > blue screwdriver | Scene 1 | 3 > red toolbox | Scene 1 | 3 > small wrench | Scene 1 | 3 > tire | Scene 2 | 3 > (9 rows) > >
Re: [GENERAL] way to custom sort column by fixed strings, then by field's content
The query is currently: select sti.description, sc.description from scene_thing_instances sti left outer join scenes sc on sti.scene_id = sc.scene_id order by sti.description, CASE when (sti.description = 'absolute root'::text) then 1 when (sti.description ilike 'root%') then 2 else 3 END; The results I want are: description| description ---+- absolute root | root 3| Scene 1 root 4| Scene 2 root 6| Scene 3 18 cm long wrench | Scene 1 blue screwdriver | Scene 1 red toolbox | Scene 1 small wrench | Scene 1 tire | Scene 2 (9 rows) On Mon, Feb 3, 2014 at 12:22 PM, Adrian Klaver wrote: > On 02/03/2014 12:01 PM, Susan Cassidy wrote: > > CCing the list. > > This didn't sort the way I want. It ended up as: >> description| description >> ---+- >> 18 cm long wrench | Scene 1 >> absolute root | >> blue screwdriver | Scene 1 >> red toolbox | Scene 1 >> root 3| Scene 1 >> root 4| Scene 2 >> root 6| Scene 3 >> small wrench | Scene 1 >> tire | Scene 2 >> >> >> The first column is the one I want sorted. >> > > Probably best to show us a made up sample of what you want. > > >> Also note that I made it a left outer join\ >> > > Where? > > >> Susan >> >> > > > -- > Adrian Klaver > adrian.kla...@gmail.com >
Re: [GENERAL] way to custom sort column by fixed strings, then by field's content
description| description ---+- 18 cm long wrench | Scene 1 absolute root | blue screwdriver | Scene 1 red toolbox | Scene 1 root 3| Scene 1 root 4| Scene 2 root 6| Scene 3 small wrench | Scene 1 tire | Scene 2 On Mon, Feb 3, 2014 at 11:14 AM, Adrian Klaver wrote: > On 02/03/2014 10:53 AM, Susan Cassidy wrote: > >> I have a column that contains items like >> 'absolute root' >> 'root 3' >> 'root 4' >> 'root 5' >> 'scene 1' >> 'scene 2' >> 'scene 3' >> >> and I would like them to sort in that order. >> >> I tried: >> select sti.description, sc.description from scene_thing_instances sti >> join scenes sc on sti.scene_id = sc.scene_id >>order by CASE sc.description >> when (sc.description = 'absolute root'::text) then 1 >> when (sc.description ilike 'root%') then 2 >> else 3 >> END; >> >> I was starting with this, and was going to add perhaps another case >> statement. >> >> But it gives me: >> ERROR: operator does not exist: text = boolean >> LINE 3: when (sc.description = 'absolute root'::text) th... >> ^ >> HINT: No operator matches the given name and argument type(s). You >> might need to add explicit type casts. >> >> I don't understand this because description is a text column, not >> boolean, and certainly 'absolute root'::text is a text string. >> >> This is 9.2. >> >> Ideas, anyone? >> > > select sti.description, sc.description from scene_thing_instances sti join > scenes sc on sti.scene_id = sc.scene_id > order by sc.description, CASE > when (sc.description = 'absolute root'::text) then 1 > when (sc.description ilike 'root%') then 2 > else 3 >END; > > >> Thanks, >> Susan >> > > > -- > Adrian Klaver > adrian.kla...@gmail.com >
[GENERAL] way to custom sort column by fixed strings, then by field's content
I have a column that contains items like 'absolute root' 'root 3' 'root 4' 'root 5' 'scene 1' 'scene 2' 'scene 3' and I would like them to sort in that order. I tried: select sti.description, sc.description from scene_thing_instances sti join scenes sc on sti.scene_id = sc.scene_id order by CASE sc.description when (sc.description = 'absolute root'::text) then 1 when (sc.description ilike 'root%') then 2 else 3 END; I was starting with this, and was going to add perhaps another case statement. But it gives me: ERROR: operator does not exist: text = boolean LINE 3: when (sc.description = 'absolute root'::text) th... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. I don't understand this because description is a text column, not boolean, and certainly 'absolute root'::text is a text string. This is 9.2. Ideas, anyone? Thanks, Susan
Re: [GENERAL] problem with grant all privileges
It turns out IT screwed something up when they installed 9.2. The actual server/client running is still pointing to 8.4. Thanks, guys. Susan On Mon, Jan 27, 2014 at 4:48 PM, Tom Lane wrote: > Susan Cassidy writes: > > This is 9.2 > > I'd bet a very good dinner that it isn't --- maybe your psql is, > but your server has to be pre-9.0. Try "select version();" to > see the actual version of the server you're connected to. > > regards, tom lane >
Re: [GENERAL] problem with grant all privileges
This is 9.2 On Mon, Jan 27, 2014 at 4:16 PM, Tom Lane wrote: > Adrian Klaver writes: > > On 01/27/2014 01:45 PM, Susan Cassidy wrote: > >> I tried: > >> GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO testuser; > >> and I get: > >> ERROR: syntax error at or near "ALL" > >> Am I misinterpreting the syntax for this? > > > Depends on what version of Postgres you are working with. The syntax of > > commands changes with time. > > That syntax works for me back to 9.0, but 8.4 gives the described error; > which is unsurprising when you examine the respective documentation. > > regards, tom lane >
[GENERAL] problem with grant all privileges
I tried: GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO testuser; and I get: ERROR: syntax error at or near "ALL" LINE 1: GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO te... also the same thing for tables, with the same results. Am I misinterpreting the syntax for this? Susan
Re: [GENERAL] problem connecting to postgres via apache
I tried:
setsebool -P httpd_can_network_connect 1
But I get:
Cannot set persistent booleans without managed policy.
Could not change policy booleans
I don't know what that means.
Susan
On Fri, Jan 24, 2014 at 9:38 AM, Adrian Klaver wrote:
> On 01/24/2014 09:35 AM, Susan Cassidy wrote:
>
>> I've already checked that. It is enabled. I am running Scientific Linux.
>>
>
> SELinux is enabled?
> The database connection value is enabled to allow or disallow webserver
> connections?
>
>
>>
>> On Fri, Jan 24, 2014 at 8:28 AM, Tom Lane > <mailto:t...@sss.pgh.pa.us>> wrote:
>>
>> Susan Cassidy > <mailto:susan.cass...@decisionsciencescorp.com>> writes:
>> > $dbh =
>> DBI->connect("dbi:Pg:dbname=$dbname;host=${dbserver};port=$dbport;",
>> > $dbuser, $dbpasswd) or
>> > errexit( "Unable to connect to dbname $dbname, err:
>> $DBI::errstr");
>>
>> > The exact same connection string works fine in a standalone perl
>> program.
>>
>> Given the permissions errors you mentioned upthread, I'm wondering
>> whether
>> you're running on Red Hat/CentOS, and if so whether SELinux is
>> preventing
>> apache from connecting to unexpected port numbers. I seem to recall
>> that there's a SELinux boolean specifically intended to allow or
>> disallow
>> database connections from webservers, but I couldn't tell you the name
>> offhand.
>>
>> regards, tom lane
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@gmail.com
>
Re: [GENERAL] problem connecting to postgres via apache
I don't actually know what SELinux is. What else will happen if I (find
out how to) disable it?
Susan
On Fri, Jan 24, 2014 at 9:47 AM, Bret Stern <
bret_st...@machinemanagement.com> wrote:
> Are you calling the perl from apache (assuming yes)..? Does the web
> user have the rights to execute the perl code?
>
> Try disabling SELinux..
> You'll get it..
>
> On Fri, 2014-01-24 at 09:35 -0800, Susan Cassidy wrote:
> > I've already checked that. It is enabled. I am running Scientific
> > Linux.
> >
> >
> >
> > On Fri, Jan 24, 2014 at 8:28 AM, Tom Lane wrote:
> > Susan Cassidy writes:
> > > $dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=
> > ${dbserver};port=$dbport;",
> > > $dbuser, $dbpasswd) or
> > > errexit( "Unable to connect to dbname $dbname, err:
> > $DBI::errstr");
> >
> > > The exact same connection string works fine in a standalone
> > perl program.
> >
> > Given the permissions errors you mentioned upthread, I'm
> > wondering whether
> > you're running on Red Hat/CentOS, and if so whether SELinux is
> > preventing
> > apache from connecting to unexpected port numbers. I seem to
> > recall
> > that there's a SELinux boolean specifically intended to allow
> > or disallow
> > database connections from webservers, but I couldn't tell you
> > the name
> > offhand.
> >
> > regards, tom lane
> >
> >
>
>
>
Re: [GENERAL] problem connecting to postgres via apache
[scassidy@jacensolo ~]$ /usr/sbin/sestatus | grep SELinux
SELinux status: enabled
SELinuxfs mount:/selinux
I have yet to find what to do to enable webserver connections.
Susan
On Fri, Jan 24, 2014 at 9:38 AM, Adrian Klaver wrote:
> On 01/24/2014 09:35 AM, Susan Cassidy wrote:
>
>> I've already checked that. It is enabled. I am running Scientific Linux.
>>
>
> SELinux is enabled?
> The database connection value is enabled to allow or disallow webserver
> connections?
>
>
>>
>> On Fri, Jan 24, 2014 at 8:28 AM, Tom Lane > <mailto:t...@sss.pgh.pa.us>> wrote:
>>
>> Susan Cassidy > <mailto:susan.cass...@decisionsciencescorp.com>> writes:
>> > $dbh =
>> DBI->connect("dbi:Pg:dbname=$dbname;host=${dbserver};port=$dbport;",
>> > $dbuser, $dbpasswd) or
>> > errexit( "Unable to connect to dbname $dbname, err:
>> $DBI::errstr");
>>
>> > The exact same connection string works fine in a standalone perl
>> program.
>>
>> Given the permissions errors you mentioned upthread, I'm wondering
>> whether
>> you're running on Red Hat/CentOS, and if so whether SELinux is
>> preventing
>> apache from connecting to unexpected port numbers. I seem to recall
>> that there's a SELinux boolean specifically intended to allow or
>> disallow
>> database connections from webservers, but I couldn't tell you the name
>> offhand.
>>
>> regards, tom lane
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@gmail.com
>
Re: [GENERAL] problem connecting to postgres via apache
I've already checked that. It is enabled. I am running Scientific Linux.
On Fri, Jan 24, 2014 at 8:28 AM, Tom Lane wrote:
> Susan Cassidy writes:
> > $dbh =
> DBI->connect("dbi:Pg:dbname=$dbname;host=${dbserver};port=$dbport;",
> > $dbuser, $dbpasswd) or
> > errexit( "Unable to connect to dbname $dbname, err: $DBI::errstr");
>
> > The exact same connection string works fine in a standalone perl program.
>
> Given the permissions errors you mentioned upthread, I'm wondering whether
> you're running on Red Hat/CentOS, and if so whether SELinux is preventing
> apache from connecting to unexpected port numbers. I seem to recall
> that there's a SELinux boolean specifically intended to allow or disallow
> database connections from webservers, but I couldn't tell you the name
> offhand.
>
> regards, tom lane
>
Re: [GENERAL] problem connecting to postgres via apache
$dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=${dbserver};port=$dbport;",
$dbuser, $dbpasswd) or
errexit( "Unable to connect to dbname $dbname, err: $DBI::errstr");
The exact same connection string works fine in a standalone perl program.
Susan
On Thu, Jan 23, 2014 at 3:08 PM, Adrian Klaver wrote:
> On 01/23/2014 02:55 PM, Susan Cassidy wrote:
>
>> I'm having a problem connecting to postgres via a CGI program. I can
>> connect just fine using the same connect string in a non-cgi perl
>> program as in the perl cgi program. The error I get is this:
>>
>>
>> ERROR: Unable to connect to dbname testdb2, err: could not
>> connect to server: Permission denied
>> Is the server running on host "jacensolo.red.dsic.com
>> <http://jacensolo.red.dsic.com>" (192.168.10.16) and accepting
>> TCP/IP connections on port 5432?
>>
>
> FYI, in the future you might want to anonymize your host information. For
> example xxx.yyy.zzz.com
>
> Well from the above it looks like you where trying to connect using TCP/IP
> not a socket.
>
> What is your connection string?
>
>
>> I tried adding postgres to the apache user's groups, because I saw
>> something on google that said that that is needed for the socket
>> connection to work, but it didn't help.
>>
>> I just tried stopping and restarting apache, and when I try to start I
>> get this:
>>
>>
>> apachectl start
>> (13)Permission denied: make_sock: could not bind to address [::]:80
>> (13)Permission denied: make_sock: could not bind to address 0.0.0.0:80
>> <http://0.0.0.0:80>
>> no listening sockets available, shutting down
>> Unable to open logs
>>
>> I took away the group permission for postgres, just in case that had
>> messed up something, but it didn't help. Now I can't even start apache.
>>
>
> To me it looks like it is already running.
>
>
>>
>> Susan
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@gmail.com
>
[GENERAL] problem connecting to postgres via apache
I'm having a problem connecting to postgres via a CGI program. I can connect just fine using the same connect string in a non-cgi perl program as in the perl cgi program. The error I get is this: ERROR: Unable to connect to dbname testdb2, err: could not connect to server: Permission denied Is the server running on host "jacensolo.red.dsic.com" (192.168.10.16) and accepting TCP/IP connections on port 5432? I tried adding postgres to the apache user's groups, because I saw something on google that said that that is needed for the socket connection to work, but it didn't help. I just tried stopping and restarting apache, and when I try to start I get this: apachectl start (13)Permission denied: make_sock: could not bind to address [::]:80 (13)Permission denied: make_sock: could not bind to address 0.0.0.0:80 no listening sockets available, shutting down Unable to open logs I took away the group permission for postgres, just in case that had messed up something, but it didn't help. Now I can't even start apache. But, if I do: [scassidy@jacensolo init.d]$ ps auxww|grep http apache 15426 0.0 0.0 186416 2740 ?SJan19 0:00 /usr/sbin/httpd -k start apache 15427 0.0 0.0 186416 2720 ?SJan19 0:00 /usr/sbin/httpd -k start apache 15428 0.0 0.0 186416 3004 ?SJan19 0:00 /usr/sbin/httpd -k start apache 15429 0.0 0.0 186416 2740 ?SJan19 0:00 /usr/sbin/httpd -k start apache 15430 0.0 0.0 186416 2724 ?SJan19 0:00 /usr/sbin/httpd -k start apache 15431 0.0 0.0 186416 2740 ?SJan19 0:00 /usr/sbin/httpd -k start apache 15432 0.0 0.0 186416 2988 ?SJan19 0:00 /usr/sbin/httpd -k start apache 15433 0.0 0.0 186416 2988 ?SJan19 0:00 /usr/sbin/httpd -k start root 18778 0.0 0.0 184208 2408 ?Ss2013 2:25 /usr/sbin/httpd -k start I'm seeing processes that apparently shouldn't be there. Maybe that's why it can't connect, because it is already connected? Should I just kill those processes, do you think? What could have happened? It was working just fine before. Very mysterious. Susan
Re: [GENERAL] Fully-automatic streaming replication failover when master dies?
pgpool-II may do what you want. Lots of people use it. On Wed, Jan 22, 2014 at 1:35 PM, Dmitry Koterov wrote: > Hello. > > I googled 1 hour approximately, but have not found a ready solution for > this. So maybe this feature is in PostgreSQL todo-list, or something > similar exists somewhere... > > Before the actual question, I'd like to give a small analogy. What I > mostly love in MongoDB is that it supports a fully transparent scheme of > replication failover. If you have >= 3 MongoDB notes (e.g. 1 master and 2 > replicas), and the master dies, in a couple of seconds a replica is > AUTOMATICALLY elected as a new master, and all other replicas are > AUTOMATICALLY begin to follow it. If the dead master is back again > suddenly, it first appears as a replica, but in a couple of seconds it > becomes a new master back (because it initially had a highest weight > assigned), and all replicas become to follow it. All these steps are done > automatically and transparently. It just works. > > So does something similar and more-or-less stable exist for PostgrSQL too? > > P.S. > The links I've found already: > - http://www.databasesoup.com - that's it, but too young and, possibly, > not yet stable > - repmgr: always referred at StackOverflow (thousands of times), but it > does not provide an automatic failover :-) > - pgpool-2: it contains a couple of config options for "failover", but in > practice they are too poor to be used in production and with large > databases, I believe after reading the docs > - pacemaker's plugin: extremely complex (IMHO) overweighted for this > purpose > - EnterpriseDB's solutions: they are not free >
Re: [GENERAL] Prepared statements and cursors
Sorry, answered wrong posting. On Thu, Jan 23, 2014 at 6:31 AM, Andreas Lubensky wrote: > Hello, > When implementing a database backend with libpq I realized that it seems > to be impossible to declare a cursor on a prepared statement. Is this > correct? What is the reason for this limitation? > > -- > with best regards, > > Andreas Lubensky > > > > -- > 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] Prepared statements and cursors
pgpool-II may do what you want. On Thu, Jan 23, 2014 at 6:31 AM, Andreas Lubensky wrote: > Hello, > When implementing a database backend with libpq I realized that it seems > to be impossible to declare a cursor on a prepared statement. Is this > correct? What is the reason for this limitation? > > -- > with best regards, > > Andreas Lubensky > > > > -- > 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] CREATE EXTENSION does not seem to work in 9.2
It wasn't installed in the yum package that we used to install postgres 9.2. I had to add it. As I said, create language worked fine. Before I did create language, I got errors that the language didn't exist when I tried to create a function. After I created the language, those errors went away. Susan On Tue, Jan 21, 2014 at 3:05 PM, Adrian Klaver wrote: > On 01/21/2014 02:51 PM, Susan Cassidy wrote: > >> When I tried to do CREATE EXTENSION IF NOT EXISTS >> /usr/pgsql-9.2/share/extension/plpgsql.control with schema public; >> >> it gave me >> ERROR: syntax error at or near "EXTENSION" >> >> CREATE LANGUAGE worked just fine. I only tried CREATE EXTENSION because >> the manual said that CREATE LANGUAGE was deprecated. >> > > If you trying to install plpgsql it is installed by default from 9.0+ > If you trying to install something else you only need the extension name > not the file path, so just plpgsql in this case. > > >> Susan >> > > > -- > Adrian Klaver > adrian.kla...@gmail.com >
[GENERAL] CREATE EXTENSION does not seem to work in 9.2
When I tried to do CREATE EXTENSION IF NOT EXISTS /usr/pgsql-9.2/share/extension/plpgsql.control with schema public; it gave me ERROR: syntax error at or near "EXTENSION" CREATE LANGUAGE worked just fine. I only tried CREATE EXTENSION because the manual said that CREATE LANGUAGE was deprecated. Susan
Re: [GENERAL] Any freeware graphic display of DDL software available?
What are the "dot" lines for? They don't seem to work on my Linux installation. I tried adding them to the initial line, and I see no .pdf output. I just see: Producing testdb.dia from /usr/share/postgresql_autodoc/dia.tmpl Producing testdb.dot from /usr/share/postgresql_autodoc/dot.tmpl Producing testdb.html from /usr/share/postgresql_autodoc/html.tmpl Producing testdb.neato from /usr/share/postgresql_autodoc/neato.tmpl Producing testdb.xml from /usr/share/postgresql_autodoc/xml.tmpl Producing testdb.zigzag.dia from /usr/share/postgresql_autodoc/zigzag.dia.tmpl The .html file just shows a table-like representation of each individual table, which is no real use to me, as I need the relationships between tables to show graphically. What do I do with a .dot or .dia formatted file? Thanks, Susan On Thu, Jan 16, 2014 at 2:06 PM, Paul Jungwirth wrote: > I use this script on an Ubuntu system: > > #!/bin/bash > > set -eu > > postgresql_autodoc -d example_dev -u example_dev -h localhost > --password= > dot -Tpng -o example-schema.png example_dev.dot > dot -Tpdf -o example-schema.pdf example_dev.dot > > That gives you a schema diagram in pdf, png, dia, and dot format, > including foreign keys. It also exports the table and column info as > html and xml. > > I'm pretty sure you can install postgresql_autodoc with apt-get, but I > don't recall for sure now. > > Good luck! > Paul > > > > > > > > > > On Thu, Jan 16, 2014 at 1:09 PM, Bartosz Dmytrak > wrote: > > Hi, > > > > try DBVisualizer > > > > > > > > -- > > > > Regards, > > > > Bartek > > > > -- > _ > Pulchritudo splendor veritatis. >
Re: [GENERAL] Any freeware graphic display of DDL software available?
It doesn't appear that DBVisualizer does an ER type diagram, which is what I really need. Thanks, Susan On Thu, Jan 16, 2014 at 1:09 PM, Bartosz Dmytrak wrote: > Hi, > > try DBVisualizer > > > > -- > > Regards, > > Bartek >
[GENERAL] Any freeware graphic display of DDL software available?
Is there any free or cheap software that will read in DDL and output a graphic display of it? Preferably showing links for foreign keys. I know about Erwin, but it is too expensive. Thanks, Susan
Re: [GENERAL] When starting postgres, it hangs like it is still connected to stdout
The initdb command said I could use that command, but pg_ctl works fine now. I should have tried that before. I have always used pg_ctl in the past, don't know why I just went with the postgres command. Thanks, Susan On Tue, Jan 7, 2014 at 11:50 AM, Shaun Thomas wrote: > > When I start postgres using postgres -D $PGDATA, it hangs, and I see that > > postgres and all the other attendant processes are running, but I never > > get my prompt back. > > Don't start PostgreSQL with the 'postgres' command. That's the name of the > actual server process. You want to use the control script, normally named > pg_ctl. You call it like this: > > pg_ctl -D $PGDATA start > > Stop it like this: > > pg_ctl -D $PGDATA stop > > __ > > See http://www.peak6.com/email_disclaimer/ for terms and conditions > related to this email >
[GENERAL] When starting postgres, it hangs like it is still connected to stdout
When I start postgres using postgres -D $PGDATA, it hangs, and I see that postgres and all the other attendant processes are running, but I never get my prompt back. If I hit ctl/C, postgres ends running. I can't seem to figure out why. This is postgres 9.2. What am I doing wrong? Thanks, Susan
[GENERAL] problem with initdb
I ran an initdb, but got a message that the Encoding I specified was invalid, so I tried to run it again, and now I get: initdb: directory "/var/lib/pgsql/data" exists but is not empty If you want to create a new database system, either remove or empty the directory "/var/lib/pgsql/data" or run initdb with an argument other than "/var/lib/pgsql/data". The only thing in the $PGDATA directory are the pg_hba.conf and postgresql.conf files. If I try to run a pg_ctl start, I get this: 17271 2014-01-07 09:10:15.060 PST FATAL: "/var/lib/pgsql/data" is not a valid data directory 17271 2014-01-07 09:10:15.060 PST DETAIL: File "/var/lib/pgsql/data/PG_VERSION" is missing. What can I do? This is postgres 8.4, which is the only version currently available for Scientific Linux in its repository. Thanks, Susan
Re: [GENERAL] problem with distinct not distincting...
I see you have the length included. Perhaps, UTF8 characters in one and ascii in the other? Susan -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Susan Cassidy Sent: Wednesday, October 17, 2012 8:27 AM To: John Beynon; pgsql-general@postgresql.org Subject: Re: [GENERAL] problem with distinct not distincting... Are you sure that one of those entries doesn't have a trailing space? Susan -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John Beynon Sent: Wednesday, October 17, 2012 3:48 AM To: pgsql-general@postgresql.org Subject: [GENERAL] problem with distinct not distincting... I have a pretty basic query; select distinct on (name) name, length(name) from drugs where customer_id IS NOT NULL order by name; which I'd expect to only return me a single drug name if there are duplicates, yet I get name | length == Roaccutane | 10 Roaccutane | 10 table encoding is UTF8... I'm scratching my head! Thanks, John. -- 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 -- 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] problem with distinct not distincting...
Are you sure that one of those entries doesn't have a trailing space? Susan -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John Beynon Sent: Wednesday, October 17, 2012 3:48 AM To: pgsql-general@postgresql.org Subject: [GENERAL] problem with distinct not distincting... I have a pretty basic query; select distinct on (name) name, length(name) from drugs where customer_id IS NOT NULL order by name; which I'd expect to only return me a single drug name if there are duplicates, yet I get name | length == Roaccutane | 10 Roaccutane | 10 table encoding is UTF8... I'm scratching my head! Thanks, John. -- 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] windows 2008 scheduled task problem
See my reply below: From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Ralph Dell Sent: Monday, February 06, 2012 8:26 AM To: pgsql-general@postgresql.org Subject: [GENERAL] windows 2008 scheduled task problem I am running postgresql 8.4.3/ postgis 1.5 on a windows server 2008 R2 I am unable to get the shp2pgsql command to run as scheduled tasks. There is no problem running any of the commands from the command line or a python script. Some sample commands are shp2pgsql -s 900913 -I -d myshape.shp gis.myshape -u xxx -p yyy > myshape.txt or %POSTGIS_PATH%\shp2pgsql -s 900913 -I -d myshape.shp gis.myshape -u xxx -p yyy > myshape.txt or shp2pgsql -s 900913 -I -d myshape.shp gis.myshape -u xxx -p yyy | psql -d mydb -U xxx -q I do not get any output from the scheduled task. The history tab in the task scheduler will show Task triggered/ task started / task completed, and report the task successfully completed. I am running the tasks under a system administrator account. Any suggestions from someone who has a similar environment. This is my first post to the list and I hope it is an appropriate place for the question I and I have provided enough detail. Ralph Dell I don't know about Win 2008, but under XP, I have to use the full path of any programs I have in scheduled job. Susan
Re: [GENERAL] SQL function and input variables
You can do something like: DECLARE p ALIAS FOR $1; in the function, if you want. Susan -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Martín Marqués Sent: Wednesday, September 21, 2011 6:09 AM To: Thomas Kellerer Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] SQL function and input variables 2011/9/21 Thomas Kellerer : > Martín Marqués, 21.09.2011 14:56: >> >> I was makeing an SQL function and got an error which on a sintax that >> I thouhgt would work: >> >> CREATE OR REPLACE FUNCTION dicInsertarPalabra(p TEXT) >> RETURNS INT AS $body$ >> INSERT INTO public.diccionario (palabra) VALUES (quote_literal(p)); >> SELECT COALESCE(codigo,0) FROM public.diccionario >> WHERE palabra = quote_literal(p); >> $body$ LANGUAGE 'SQL'; >> >> Changing p for $1 in the body of the function makes it work. But, >> can't we label input arguments like how I did here? >> > This is because the language SQL does not support named parameters, only > positional ones. > > http://www.postgresql.org/docs/current/static/xfunc-sql.html#XFUNC-NAMED-PARAMETERS Thanks for the tip. Didn't know that, and just kept using plpgsql sintax. Thanks again. -- Martín Marqués select 'martin.marques' || '@' || 'gmail.com' DBA, Programador, Administrador -- 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] Backend process that won't die
-Original Message- > From: Tom Lane [mailto:t...@sss.pgh.pa.us] > Sent: Tuesday, September 06, 2011 9:57 AM > To: Susan Cassidy > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Backend process that won't die > Susan Cassidy writes: >> I have a couple of backend processes that are "stuck", and do not respond to >> a pg_cancel_backend. This is PostgreSQL 8.3.5. The pg_cancel_backend >> returns true, but the process keeps running. I have also done a "kill >> 12345" from the command-line, with no effect. >> We had a database crash last week, and had to reindex a bunch of tables, but >> this function has been working for several days on the same tables that >> should be being used by the function_x function. > By "this function" you mean that the reindex is not finished, but nonetheless you have got regular queries running with the corrupted indexes? No, the reindexes that I knew were needed have already been done. > Any ideas on how to get the processes to go away? > It seems like a good bet that they're chasing circular links in the corrupted indexes. "kill -9" would get rid of them, but it would force a database-wide restart, which would also take out your reindex process, so maybe that wouldn't be a good idea. > If they're significantly interfering with the progress of the reindex then maybe you should bite the bullet and kill them anyway. Otherwise I'd be inclined to let them go until you can afford a restart. > regards, tom lane Without any error messages about indexes, which I have not seen lately, I have no idea which indexes still might need rebuilding. So, you think I should go ahead and kill -9 the "stuck" processes, and let the database restart? It is a 2-system cluster, with failover, so I'll let the IT guy handle that, I guess. Thanks, Susan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Backend process that won't die
I have a couple of backend processes that are "stuck", and do not respond to a pg_cancel_backend. This is PostgreSQL 8.3.5. The pg_cancel_backend returns true, but the process keeps running. I have also done a "kill 12345" from the command-line, with no effect. The processes are running a "select function_x" statement that normally takes a fraction of a second to run. No locks are shown when I do: select relname,pg_locks.* from pg_class,pg_locks where relfilenode=relation and not granted; We had a database crash last week, and had to reindex a bunch of tables, but this function has been working for several days on the same tables that should be being used by the function_x function. Any ideas on how to get the processes to go away? They are eating cpu cycles, for no good reason: postgres 28396 85.0 1.4 4420768 242224 ? Ss Sep03 3193:40 postgres: userxx dbname1 172.27.43.9(1160) SELECT Thanks, Susan
Re: [GENERAL] SQL-ASCII database cleanup
Use the Encode module to test/convert back and forth between UTF8 characters
and bytes for the SQL ASCII database. Assuming the input is already UTF-8:
use Encode qw(:all);
# connect to db, prepare insert statement, etc.
my $bytes = encode('utf8', $utf8_text);
$sth->execute($bytes, $i) or errexit("execute of insert into public_suffixes
tbl failed: ", $DBI::errstr);
If your input is not already UTF-8, you will have to use decode in an eval
statement to convert to utf-8, then check for failure before re-converting and
inserting into the database. Or something similar.
This seems to work for me. When I need to pull the data back out of the
database, I have to reconvert from the byte string into UTF-8 characters before
displaying the output.
Susan
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Mike Blackwell
Sent: Thursday, July 21, 2011 7:49 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] SQL-ASCII database cleanup
I have an older database that was created with SQL-ASCII encoding. Over time
users have managed to enter all manner of interesting characters, mostly via
cut and paste from Windows documents. I'm attempting to clean up and
eventually the database to UTF8. I've managed to find most of the data that
won't nicely convert from some-random-encoding to UTF8, but it seems the users
are entering it as fast as I can find it. Is there a way the incoming data from
a Perl CGI web application can be automatically limited to UTF8 even though the
database is SQL-ASCII?
Mike
Re: [GENERAL] Error Importing CSV File
There seems to be no reason it should be looking for an integer, if your table definition as shown is correct. You don't have any integers listed. Also, why does it think that the column id is 96799? Stupid question, but are you logged into the right database? Maybe a different db has a different table definition for that table name? Maybe it is using a different file than you think it is? I had to specify the full path to get the COPY to work on my test database. When I created the same table as you specified, and created a test CSV file, it worked fine both with and without quotes. Susan -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Bryan Nelson Sent: Friday, July 15, 2011 9:04 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Error Importing CSV File I am having problems importing a CSV file of sample data for testing in a web app. Columns & Types --- zip_code - text lattitude - float8 longitude - float8 city - text state - text county - text Some Sample Data From CSV File -- 96799,-7.209975,-170.7716,PAGO PAGO,AS,AMERICAN SAMOA 96941,7.138297,151.503116,POHNPEI,FM,FEDERATED STATES OF MICRO 96942,7.138297,151.503116,CHUUK,FM,FEDERATED STATES OF MICRO COPY Command COPY geo_data FROM 'geo_data2.csv' DELIMITERS ',' CSV; Error Message - ERROR: invalid input syntax for integer: "96799" CONTEXT: COPY geo_data, line 1, column id: "96799" I can't figure out why it keeps choking with "invalid syntax for integer" since the field was created as "text". Any and all help greatly appreciated! -- 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 Importing CSV File
The default quote character is ", so I believe it is expecting quotes to be around text fields. That is the norm for CSV files. Susan -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Bryan Nelson Sent: Friday, July 15, 2011 9:04 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Error Importing CSV File I am having problems importing a CSV file of sample data for testing in a web app. Columns & Types --- zip_code - text lattitude - float8 longitude - float8 city - text state - text county - text Some Sample Data From CSV File -- 96799,-7.209975,-170.7716,PAGO PAGO,AS,AMERICAN SAMOA 96941,7.138297,151.503116,POHNPEI,FM,FEDERATED STATES OF MICRO 96942,7.138297,151.503116,CHUUK,FM,FEDERATED STATES OF MICRO COPY Command COPY geo_data FROM 'geo_data2.csv' DELIMITERS ',' CSV; Error Message - ERROR: invalid input syntax for integer: "96799" CONTEXT: COPY geo_data, line 1, column id: "96799" I can't figure out why it keeps choking with "invalid syntax for integer" since the field was created as "text". Any and all help greatly appreciated! -- 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] Read MS-SQL data into Postgres via ODBC link?
>From: pgsql-general-ow...@postgresql.org >[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Jonathan Brinkman >Sent: Tuesday, July 05, 2011 7:48 AM >To: pgsql-general@postgresql.org >Cc: 'Brent Wood' >Subject: Re: [GENERAL] Read MS-SQL data into Postgres via ODBC link? > >Thanks >I'm importing into Postgresql 8.4.8 from MSSQL 2005. > >I do not have control over the MSSQL server, it is at a customer's site. I >only have access to read-only views on their server, from which I replicate >the data to my postgres staging tables. > >I cannot have the MSSQL server do anything like create .csv files, since it's >not my server and the customer will not do that kind of thing (or create DTS >scripts, etc. from the PUSH side). > >I'm hoping there is a way I can simply SELECT from an ODBC source and use that >SELECT as part of a Postgres INSERT statement. >I don't use MySQL for anything. >Thanks much for your response! > >J Just connect to both databases (in Perl or Python, or something), do a select on the MSSQL data on the MSSQL handle. When you get the row into a set of column variables, just insert the columns via the Postgres handle into the Postgres database. Simple as pie. You can prepare the INSERT statement so that multiple executions use bind variables. Commit every 1000 rows or whatever. Susan
Re: [GENERAL] { SELECT *->NOT(column1, column2) FROM table } syntax idea
Wouldn't it be easy enough to write a little program to suck in the column
names from the information schema, and output the CREATE VIEW statement,
excluding all the columns you want to exclude?
Then, if the tables have changed, just run the program, let it fetch the
information from the information schema, do the DROP and CREATE, and you're
done.
Susan C.
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of David Johnston
Sent: Wednesday, June 15, 2011 3:08 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] { SELECT *->NOT(column1, column2) FROM table } syntax idea
Is there, or has there ever been, a discussion about introducing syntax to
handle specifying which columns you do NOT want to output in the SELECT list?
The use case I am running into is mostly within VIEWS. I want to specify
"SELECT * FROM table" but there are a couple of fields that I do NOT want to
output (for instance a password hash field for a user table). I guess it would
probably be better form to move those columns to a separate enhanced
permissions table but since PostgreSQL allows for per-column permissions that
is not strictly necessary. Listing all the wanted columns is not desirable
though obviously possible. The main reason to avoid doing so is to allow for a
view to output all the columns of the underlying tables. If I drop/create the
view after altering the underlying tables the new view will have the additional
columns without any direct change to the view being required.
David J.
Re: [GENERAL] Remove Modifiers on Table
> Doesn't the SERIAL shortcut automatically do this on the fly? How
> would I set this?
>
> ALTER TABLE table_name ALTER COLUMN id SET DEFAULT nextval('foo_seq_id');
If you have existing data, say with values 1, 2, 3, etc. and you set the column
to start using a sequence nextval as default, unless the sequence has been told
what value to start with, it will start at 1.
Per the documentation:
SELECT setval('users_id_seq', 42); -- Next nextval (insert) will return 43
Susan Cassidy
--
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] Remove Modifiers on Table
Don't forget to use setval to set the current value of the sequence to the highest number used in the data already, so that the next insertion uses a new, unused value. Susan Cassidy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] revoke permissions - not working as expected
OK, I must be doing something wrong. I'm trying to create a user with only
limited access to certain tables. The db is large, complicated, and has tons
of users with some complex interactions of permission using groups, etc. I
don't dare revoke any exist permissions, for fear of messing up a production db.
Version is 8.3.9.
I'm currently testing this on the test version of the db, though, which is a
clone of the production db. I've edited the actual usernames, table names, db
names, etc.
I created a role usera.
I revoked everything I could think of (e.g. REVOKE ALL PRIVILEGES ON SCHEMA
public FROM usera cascade;, revoke all privileges on database maindb from usera
cascade; I also did individual tables:
urldb=# revoke select, update, delete on table1 from usera; ( I
tried this with and without CASCADE - no difference)
REVOKE
urldb=# \q
sb-dev-testdb:~# psql -U usera maindb
Welcome to psql 8.3.9, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
urldb=> select * from table1;
All rows display anyway.
\dp table1 shows:
Access privileges for database
"maindb"
Schema | Name| Type | Access
privileges
+---+---+---
public | languages | table |
{postgres=arwdxt/postgres,=arwdxt/postgres,group1=r/postgres,group2=arwdxt/postgres}
(1 row)
I'm sure it's something simple that I'm doing wrong.
Advice?
Thanks,
Susan
Re: [GENERAL] postgres conferences missing videos?
Personally, I vastly prefer written information to video. I can read far faster than someone can read/explain something to me. Plus, it is easier to refer back to. Susan -- 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] constraining chars for all cols of a table
> From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Gauthier, Dave > Sent: Friday, February 18, 2011 1:24 PM > To: pgsql-general@postgresql.org > Subject: [GENERAL] constraining chars for all cols of a table > Hi: > I have to constrain the chars used for table columns. For example... >create table foo (col1 text, col2 text, col3 text); > ... where > col1 has to be all uppercase, nothing but [A-Z] > col2 has to be all lowercase [a-z] plus [0-9] is also allowed > col3 can be mixed case plus [0-9] and sqr brackets (but nothing else). > I could put a check constraint on each/every table column, but that seems > complicated and potentially slow. > I could do this check using the existing insert and update before triggers, > but then I'd have to loop through all > the columns and decide one by one how to check them. Again, slow and > complicated. > Is there a better way? Whatever I do, I'd have to be able to capture > violations to provide informative feedback > to the users through the perl script that'll actually be doing the > insert/update. I would normally do this in the perl script, using regexes, and not allowing the insert if the input data failed to pass the tests. If this is a CGI program, it is easy to use JavaScript to validate before submitting to the CGI script. Of course, you have to ensure that every program that inserts or updates data checks the constraints. Or put them in the database, or both. Susan
Re: [GENERAL] OOO and postgres
Per the error message, you need to enclose array values in braces. For
example, something like:
INSERT into "Bladetypes" ("ID", "type"), values ('1', '{"Knife"}');
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Bernhard Rohrer
Sent: Friday, January 07, 2011 9:41 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] OOO and postgres
Hi guys
I am using the postgres driver for OOO and just ran into the following
error:
Error code: 1
pq_driver: [PGRES_FATAL_ERROR]ERROR: array value must start with "{" or
dimension information
LINE 1: ...O "public"."Bladetypes" ( "ID","type") VALUES ( '1','Knife')
^
(caused by statement 'INSERT INTO "public"."Bladetypes" ( "ID","type")
VALUES ( '1','Knife')')
the table looks like this:
CREATE TABLE "Bladetypes"
(
"ID" integer NOT NULL,
"type" character varying[] NOT NULL,
CONSTRAINT "Bladetypes_pkey" PRIMARY KEY ("ID")
)
ALTER TABLE "Bladetypes" ADD COLUMN "ID" integer;
ALTER TABLE "Bladetypes" ALTER COLUMN "ID" SET NOT NULL;
ALTER TABLE "Bladetypes" ADD COLUMN "type" character varying[];
ALTER TABLE "Bladetypes" ALTER COLUMN "type" SET NOT NULL;
is this for this list?
Thanks
Bernhard
--
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] Defining a Connection String in Windows XP
>Ray, > >Thank you for responding. OK, that was my first trip into DSN setup >and I got lost: >The first step is to choose and existing MS driver for an Office app >or 'Add' one from this 'User DSN' tab. The other tabs are: >System dSN, File DSN, Drivers, Tracing, and Connection Pooling. >I did not see a pgsql listed in any - what is the next step (or two)? > >ray I usually use a System DSN. If, when you click Add, you don't see 'PostgreSQL ANSI' as one of the choices, you don't have the ODBC driver installed. If you do, just fill in the boxes with the database name, password, etc. Susan C. -- 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] how do i count() similar items
Use a CASE statement? something like: select case WHEN os ~* E'^windows' then 'windows' WHEN os ~* E'server' then 'server' WHEN os ~* E'nix$' then '*nix' else 'other' end as osval, count(*) from os_tbl group by osval order by osval; The hard part is making sure your regexes cover all the bases, without duplication. It still sounds like the value should be a reference to a unique value in a small table of operating system entries, then store the value, rather than the string, in the main table. Susan -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of jackassplus Sent: Wednesday, September 08, 2010 12:22 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] how do i count() similar items > To ensure data integrity, > you should probably create a fruit_type table with a unique column that > lists the possible types, and then foreign key the fruit_type column in > the fruits table to that to ensure nothing funky is entered. An enum > for type is another possibility. In the real world, this column actiually holds Operating Systems. I have 7 variants of Windows XP, even more of server, a dozen *nixes, etc, etc and it is fed from an external app. So I am looking for a magic query, or even a perl function to wrap up insde a procedure, whatever. -- 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] weird empty return from select problem; periodically get no data returned - could it be a network issue?
I have a number of Perl programs of similar form to this:
$dbh=DBI->connect("dbi:Pg:dbname=$dbname;host=${dbserver};", $dbuser, $dbpasswd,
{PrintError => 0, PrintWarn => 0, AutoCommit => $autocommit}) or
errexit( "Unable to connect to dbname $dbname, err: $DBI::errstr");
errexit("No db handle") unless ($dbh);
#update statement definition here
my $update_info_sth=$dbh->prepare($stmt) or errexit("Cannot prepare handle for
$stmt; ", $DBI::errstr);
#stmt=select statement definition here; selects some data ordered by date,
limit n, where n is about 300 or so, depending on the exact program
my $select_info_sth=$dbh->prepare($stmt) or errexit("Cannot prepare handle for
$stmt; ", $DBI::errstr);
trace_output("after prepare of select stmt");
$select_info_sth->execute() or errexit("Cannot execute select_info_sth;
",$select_info_sth->errstr);
trace_output("after execute of select stmt");
my (%info, @data);
trace_output("fetching domain info");
while (@data = $select_info_sth->fetchrow_array) {
foreach (@data) { $_='' unless defined}
next if ($data[0] eq '');
$info{$data[0]}=$data[1];
$update_sth->execute($data[0]) or errexit("Cannot update table processing
column for id $data[0]; ",$update_sth->errstr);
trace_output("processing set true for id $data[0], dom: $data[1]");
}
##check for problems with premature termination
errexit("Error in fetching:", $select_info_sth->errstr) if
$select_info_sth->err;
#not really an error, just nothing to process:
if ((scalar keys %info) == 0) {
trace_output("No ids returned");
$dbh->disconnect;
exit 0;
}
The trace_output and errexit subroutines are standard logging-type things.
After the SELECT runs, the program should take the ids returned, and process
each, doing whatever it is supposed to do. The SELECT, in this case, is
ordering data by a date, so that we are processing the oldest data. Therefore,
data should always be returned.
This is a pg cluster installation, using version 8.3.5.
Many instances of these programs run all day long, some on a regular Debian
Lenny server, others through exec hosts in a Sun Grid. Most of the time, data
is returned, and the program proceeds along its way, no problem.
Periodically (I see no pattern to the times), the program will exit with the
"No ids returned" message in the log. No errors or anything are in the
database log, that I can find. I have seen in the log processes connecting and
running the main SELECT at apparently the appropriate time, then a "rollback"
(presumably due to the disconnect), and disconnect.
I don't really understand why the query returns nothing periodically, then
works fine again seconds later. The database server is quite busy, doing
thousands of queries all the time.
Any explanations or ideas? The processing works, because other iterations of
the program are constantly running, so the next attempt returns data, and runs
as normal. However, it bugs me that sometimes a query that should work is
returning no results, for no discernable reason.
Thanks,
Susan
Re: [GENERAL] Trigger for modification timestamp column
Can't you check it using something like this: IF OLD.modified = NEW.modified THEN NEW.modified = NOW(); END IF; RETURN NEW; Susan -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Johan Andersson Sent: Wednesday, July 07, 2010 6:49 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Trigger for modification timestamp column Hello! I am trying to write a trigger for updating a modification column and am having some trouble getting it to behave as I want. The trigger should set the column to the supplied value if it is set in the UPDATE statement and to the current timestamp [NOW()] if it is not. The problem is that I don't know how to check if the column is set or not. I can check the column's value for NULL but that doesn't work if I want the column to accept NULL values (meaning "unmodified"). I would like something like: CREATE FUNCTION update_modified() RETURNS TRIGGER AS $$ BEGIN IF NOT isset(NEW.modified) THEN NEW.modified = NOW(); END IF; RETURN NEW; END; $$ LANGUAGE 'plpgsql'; Does anyone know how to do this? Thanks in advance! / Johan -- View this message in context: http://old.nabble.com/Trigger-for-modification-timestamp-column-tp29096359p29096359.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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] question about log entry from trigger execution
I have a database I inherited with a number of triggers defined on various tables. One AFTER trigger, when executed, causes a database log entry with a "CONTEXT" entry, but no ERROR. I normally only see the CONTEXT entries when an ERROR occurs. The trigger is on table rbs, and it returns NULL. Here are the entries (slightly edited): May 5 16:08:21 postgres[18723]: [598-1] 2010-05-05 16:08:21 UTC LOG: statement: DELETE FROM rbs WHERE id = 136259855 AND cid = 601 May 5 16:08:21 postgres[18723]: [599-3] 2010-05-05 16:08:21 UTC CONTEXT: SQL statement "UPDATE uinfo UI SET id_bitmap = (id_bitmap & ~(1::bit(128) << (C.bitmap_index May 5 16:08:21 postgres[18723]: [599-4] - 1)) ) FROM cinfo C WHERE UI.id = $1 AND $2 = C.cid" May 5 16:08:21 postgres[18723]: [599-5] ^IPL/pgSQL function "rbs__adel" line 6 at SQL statement May 5 16:08:21 postgres[18723]: [600-1] 2010-05-05 16:08:21 UTCLOG: statement: COMMIT The statement that seems to be logged is: UPDATE uinfo UI SET id_bitmap = (id_bitmap & ~(1::bit(128) << (C.bitmap_index - 1)) ) FROM cinfo C WHERE UI.id = OLD.id AND OLD.cid = C.cid; The update seems to get done ok. I still wonder what the log entry is trying to tell me. This is PostgreSQL 8.3. Any ideas? Susan
[GENERAL] Before update trigger causing another after trigger to fire, returning NULL, causing before trigger to not update - does this make sense?
Hi, I was trying to update certain columns in a table, but the update never took place, but did not throw an exception. I investigated the code in the 'before update' trigger, and put in some RAISE NOTICE statements. I could then see that another update of the same table was happening, too, but the last return values in the last (outer) NEW record returned by the before trigger seemed fine. I then found that an additional UPDATE statement in the original 'before update' trigger was firing an 'after update' trigger on another table, which returned NULL. Sequence of events when problem occurred: update table a causes update table b which updates table a again (different column) trigger for table b returns null update of table a does not happen After eliminating the call to the UPDATE statement for the second table (so that no NULL was returned), the original trigger started working as desired (e.g. columns were updated). I know the documentation says 'If any before trigger returns NULL, the operation is abandoned for that row and subsequent triggers are not fired.' However, the NULL was returned by an AFTER trigger, and not a BEFORE trigger, but it was within the cascade of events generated by the BEFORE trigger. This is Postgres 8.3.9 (I know, but upgrading would be a major undertaking). The combination of data I was trying to update cannot occur with the current set of triggers, etc., so it is not unreasonable that this particular combination of events was unforeseen when the triggers were written. Could this have really caused my problem? Is this how it is supposed to work? Or, is my hypothesis that the NULL caused the update to fail just a coincidence, and I should do further debugging? Thanks, Susan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
