[GENERAL] Mixing use of ECPG and libpq
Hello all, I'm trying to write a postgres client application in C. I'm having to make use of some existing in-house code that soley uses ECPG to interact with a number of databases. However, I need to exploit some lower-level libpq functionality directly. Does anyone know how I can get hold of a PGconn* handle for an already-open connection, when all I have is the connection name that ECPG uses to identify it? Sadly, opening the connection myself with PGconnectdb isn't feasible as the in-house code (that's actually very good!) has multiple layers of abstraction that allow databases to be moved around between machines, while keeping backups etc ticking away seemlessly. But, as I say, it creates connections with ECPG. Many thanks, Nathaniel -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] array datatype supported by Perl DBI with Postgres DBD ?
Does anyone know if the Postgres DBD for the Perl DBI supports the array datatype? Thanks in Advance !
Re: [GENERAL] Adding integers ( > 8 bytes) to an inet
On Tue, Sep 08, 2009 at 05:11:02PM +0100, Sam Mason wrote: > On Tue, Sep 08, 2009 at 05:58:01PM +0200, Kristian Larsson wrote: > > On Tue, Sep 08, 2009 at 11:37:02AM -0400, Tom Lane wrote: > > > I think the whole thing is a bit of a crock; adding integers to inet > > > addresses doesn't make a lot of sense logically. Perhaps what is > > > really wanted is functions on CIDR net identifiers, for instance > [...] > > For me, as a network engineer, adding an integer to a inet feels > > quite natural. Inet is just another representation of a integer > > anyway... so I'd really not have a problem with having either a > > int16 or being able to add numerics to inets :) > > Indeed, it seems similar to the (somewhat arbitrary) decision that > adding an int to a date results that many days being added to it. > Timestamp INTERVALs may be more flexible, but it's a useful shortcut > that I use quite often. > > Something to convert to/from a NUMERIC value and INET would seem useful > as well. I'd like to reach some form of consensus on what to do about this. Do we a) ignore it and let users use the workarounds? b) add a next_address() as per Toms suggestion ? c) add a conversation between NUMERIC and INET so one can add a NUMERIC to an INET just as is possible today with INTEGERs? While Tom's suggestion about next_address might be convenient in certain scenarios I think it would be nice to be able to add a numeric to an inet. In other database systems you typically don't have a inet type at all so people who handle IP addresses in databases are used to working with integers and bit shifting et al to do all the IP calculations that one might need. Based on thie, I vote for option C. What say you? Yay or nay? :) Kind regards, Kristian. -- Kristian LarssonKLL-RIPE +46 704 264511k...@spritelink.net -- 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] Adding integers ( > 8 bytes) to an inet
Kristian Larsson writes: > Do we > a) ignore it and let users use the workarounds? > b) add a next_address() as per Toms suggestion ? > c) add a conversation between NUMERIC and INET so one can add a > NUMERIC to an INET just as is possible today with INTEGERs? I vote for (a). It was already pointed out that you can build next_address and the other related functions out of the existing operations, so proposal (b) wouldn't buy much. Proposal (c) is disingenuous because it ignores the fact that NUMERIC does not have (and cannot easily implement) most of the bitwise operations that people might think they want here. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] "show all" command crashes server
Hi folks First time poster here so please extend grace if I don't initially provide what is needed to help. I am running postgresql 8.3.7 on debian lenny (postgresql-8.3_8.3.7-0lenny1_i386.deb). I have three of these servers and generally they run well. On this one server if I use the command "show all" in psql, phpPgAdmin or pgAdmin3 the postgresql server spits the dummy as follows: postg...@theconsole:~$ psql Welcome to psql 8.3.7, 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 postgres=# show all; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. In the syslog is: Sep 10 23:55:14 theconsole postgres[31118]: [3-2] 0: LOCATION: reaper, postmaster.c:2156 Sep 10 23:55:15 theconsole postgres[31124]: [4-1] [local] [unknown] [unknown] 0: LOG: 08P01: incomplete startup packet Sep 10 23:55:15 theconsole postgres[31124]: [4-2] [local] [unknown] [unknown] 0: LOCATION: ProcessStartupPacket, postmaster.c:1396 Sep 10 23:55:36 theconsole postgres[31118]: [4-1] 0: LOG: 0: server process (PID 31145) was terminated by signal 11: Segmentation fault Sep 10 23:55:36 theconsole postgres[31118]: [4-2] 0: LOCATION: LogChildExit, postmaster.c:2529 Sep 10 23:55:36 theconsole postgres[31118]: [5-1] 0: LOG: 0: terminating any other active server processes Sep 10 23:55:36 theconsole postgres[31118]: [5-2] 0: LOCATION: HandleChildCrash, postmaster.c:2374 Sep 10 23:55:36 theconsole postgres[31118]: [6-1] 0: LOG: 0: all server processes terminated; reinitializing Sep 10 23:55:36 theconsole postgres[31118]: [6-2] 0: LOCATION: PostmasterStateMachine, postmaster.c:2690 Sep 10 23:55:36 theconsole postgres[31146]: [7-1] 0: LOG: 0: database system was interrupted; last known up at 2009-09-10 23:55:14 EST Sep 10 23:55:36 theconsole postgres[31146]: [7-2] 0: LOCATION: StartupXLOG, xlog.c:4836 Sep 10 23:55:36 theconsole postgres[31147]: [7-1] [local] postgres postgres 0: FATAL: 57P03: the database system is in recovery mode Sep 10 23:55:36 theconsole postgres[31147]: [7-2] [local] postgres postgres 0: LOCATION: ProcessStartupPacket, postmaster.c:1648 Sep 10 23:55:36 theconsole postgres[31146]: [8-1] 0: LOG: 0: database system was not properly shut down; automatic recovery in progress Sep 10 23:55:36 theconsole postgres[31146]: [8-2] 0: LOCATION: StartupXLOG, xlog.c:5003 Sep 10 23:55:36 theconsole postgres[31146]: [9-1] 0: LOG: 0: record with zero length at 2A/E734761C Sep 10 23:55:36 theconsole postgres[31146]: [9-2] 0: LOCATION: ReadRecord, xlog.c:3126 Sep 10 23:55:36 theconsole postgres[31146]: [10-1] 0: LOG: 0: redo is not required Sep 10 23:55:36 theconsole postgres[31146]: [10-2] 0: LOCATION: StartupXLOG, xlog.c:5146 Sep 10 23:55:36 theconsole postgres[31150]: [7-1] 0: LOG: 0: autovacuum launcher started Sep 10 23:55:36 theconsole postgres[31150]: [7-2] 0: LOCATION: AutoVacLauncherMain, autovacuum.c:520 Sep 10 23:55:36 theconsole postgres[31118]: [7-1] 0: LOG: 0: database system is ready to accept connections this is 100% repeatable. The database seems to work fine unless this command is run then it is instant death. any help would be appreciated regards Grant
Re: [DOCS] [GENERAL] What happens when syslog gets blocked?
Alvaro Herrera wrote: > decibel wrote: > > On Aug 6, 2009, at 2:00 PM, Bill Moran wrote: > > > >Well ... "life better" really depends on which failure scenario you're > > >more comfortable with ... personally, I'd rather lose log messages > > >than > > >have the DB system go down. Of course, if auditing is critical to > > >your > > >scenario, then your priorities are different ... > > > > Bingo. I'm thinking we should make mention of this in the docs... > > I propose the following patch. Committed (on HEAD) -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] "show all" command crashes server
Grant Maxwell wrote: > Hi folks > > First time poster here so please extend grace if I don't initially > provide what is needed to help. > > I am running postgresql 8.3.7 on debian lenny > (postgresql-8.3_8.3.7-0lenny1_i386.deb) Well that's useful. > I have three of these servers and generally they run well. As is that. > On this one server if I use the command "show all" in psql, phpPgAdmin > or pgAdmin3 the postgresql server spits the dummy as follows: > postgres=# show all; > server closed the connection unexpectedly Hmm - some modules can provide their own config variables. Do you have the same modules installed in all three servers? Can you "show" individual variables? -- Richard Huxton Archonet Ltd -- 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] Adding integers ( > 8 bytes) to an inet
On Thu, Sep 10, 2009 at 10:30:49AM -0400, Tom Lane wrote: > Kristian Larsson writes: > > Do we > > c) add a conversation between NUMERIC and INET so one can add a > > NUMERIC to an INET just as is possible today with INTEGERs? > > Proposal (c) is disingenuous because it ignores the fact that NUMERIC > does not have (and cannot easily implement) most of the bitwise > operations that people might think they want here. Huh, good point. What you want is a finite field; which looks exactly like what the "bit" type is for. Why not use that? You can't cast them to or from numeric which is a bit annoying, but doesn't seem too hard in principle. -- Sam http://samason.me.uk/ -- 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] array datatype supported by Perl DBI with Postgres DBD ?
On Thu, Sep 10, 2009 at 9:41 AM, Gauthier, Dave wrote: > Does anyone know if the Postgres DBD for the Perl DBI supports the array > datatype? I use numeric array columns with DBD::Pg. What do you define as "support"? -- 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] array datatype supported by Perl DBI with Postgres DBD ?
I plan to use text arrays. I have an old O'Reilly "Programming the Perl DBI" and it doesn't mention supoprt of these things. I suppose that this might be because some DBs don't support them. But PG does, and I was wondering how it would pass column data which is an array when using something like fetchall_arrayref(). Is there a doc for this somewhere? I googled around for a while and didn't see anything. Thanks -dave -Original Message- From: Vick Khera [mailto:vi...@khera.org] Sent: Thursday, September 10, 2009 12:19 PM To: Gauthier, Dave Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] array datatype supported by Perl DBI with Postgres DBD ? On Thu, Sep 10, 2009 at 9:41 AM, Gauthier, Dave wrote: > Does anyone know if the Postgres DBD for the Perl DBI supports the array > datatype? I use numeric array columns with DBD::Pg. What do you define as "support"? -- 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] "show all" command crashes server
On Thu, Sep 10, 2009 at 8:37 AM, Grant Maxwell wrote: > Hi folks > First time poster here so please extend grace if I don't initially provide > what is needed to help. > I am running postgresql 8.3.7 on debian lenny > (postgresql-8.3_8.3.7-0lenny1_i386.deb). > I have three of these servers and generally they run well. SNIP > Sep 10 23:55:36 theconsole postgres[31118]: [4-1] 0: LOG: 0: server > process (PID 31145) was terminated by signal 11: Segmentation fault Sig 11 is a process crash which can be caused by bad hardware or corrupted / buggy binaries. I'd try reinstalling pgsql binaries and see if that helps. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] WAL archiving file name collision
Berge Schwebs =?utf-8?Q?Bj=C3=B8rlo?= writes: > Recently, WAL archiving begain failing on the test which checks wether the > file exists. This first occured two hours after an incident where someone > edited pg_hba.conf and left it with permissions denying Postgres read access > to it. Upon SIGHUP the cluster naturally shut down. FWIW, versions later than 8.3 don't "naturally shut down" for that; they'll just keep running with the old settings. > It was discovered > promptly, and according to this person, there were "some processes named > postgres still running". He ran "/etc/init.d/postgresql-8.3 start" anyway, > which brought up the cluster: If there were old backends still running then the postmaster should not have started. I have a nasty feeling that you have one of the start scripts that takes it upon itself to blow away the postmaster.pid file, which is a necessary part of the interlock that prevents that from happening. If that happened, you would have had some old backends running with one idea of the current xlog location, and some other backends running with another idea of the current xlog location, and it would not have taken long for the database to get completely scrambled :-(. The duplicated WAL segment file would be an unsurprising consequence of that, but I'm much more worried about what happened to your data because of duplicate XID numbers. Have you seen any evidence of data corruption on the master database? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] query speed question
Hi Alban- > > I have two tables that are georeferenced (although in this case I'm > > not using PostGIS) that I need to join. > > A ( lat | lon | depth | value) > > |A| = 1,100,000 > > > > B ( lat | lon | attributes) > > |B| = 14,000,000 > > > > A is a special case because the lat / lon values are all at half > > degree intervals (ie 33.5, -12.5). The lat/lons in B are arbitrary. > > I've written a function in B called getSpecialLat(latitude) and > > getSpecialLon(longitude) to calculate the correct A latitude and > > built an index on both functions. > > My guess is that those functions round lat and lon values to their > nearest half-degree interval counterpart as in table A? > I assume you marked that function immutable? > Is the return type indeed a numeric, as there are some explicit casts > in the query plan? Yes - the function is immutable. The return type is numeric, but I fixed the casting on input problem... > > Here's the query that I'm trying, but it's rather slow: > > SELECT B.* FROM B, > > (SELECT lat, lon FROM A WHERE value > 0 AND value < 2 AND depth = 0) > > AS foo > > WHERE getSpecialLat(B.lat) = foo.lat AND getSpecialLon(B.lon) = > > foo.lon > > > > "Nested Loop (cost=3569.88..32055.02 rows=1414 width=422)" > > " -> Index Scan using A_valueidx on A (cost=0.00..555.26 rows=6 > > width=16)" > > "Index Cond: ((value > 0) AND (value < 2))" > > "Filter: (depth = 0)" > > " -> Bitmap Heap Scan on B (cost=3569.88..5029.48 rows=424 > > width=422)" > > "Recheck Cond: ((getSpecialLon((B.lon)::numeric) = A.lon) > > AND (getSpecialLat((B.lat)::numeric) = A.lat))" > > "-> BitmapAnd (cost=3569.88..3569.88 rows=424 width=0)" > > " -> Bitmap Index Scan on Blonidx (cost=0.00..1760.38 > > rows=84859 width=0)" > > "Index Cond: (getSpecialLon((B.lon)::numeric) = > > A.lon)" > > " -> Bitmap Index Scan on Blatidx (cost=0.00..1766.81 > > rows=84859 width=0)" > > "Index Cond: (getSpeicalLat > > ((B.latitude)::numeric) = A.lat)" > > > > Am I missing something in terms of speeding up this query? > > > Hard to tell without knowing where most time gets spent. An EXPLAIN > ANALYSE would tell. Here are both queries (with explain analyze): EXPLAIN ANALYZE SELECT B.* FROM B, (SELECT lat, lon FROM A WHERE value > 0 AND value < 2 AND depth = 0) AS foo WHERE getwoalatitude(B.latitude::numeric) = foo.lat AND getwoalongitude(B.longitude::numeric) = foo.lon after vacuum analyze: "Merge Join (cost=17873237.91..26594735.94 rows=1984509 width=420) (actual time=752983.201..941125.197 rows=226941 loops=1)" " Merge Cond: ((a.lat = (getwoalatitude((b.latitude)::numeric))) AND (a.lon = (getwoalongitude((b.longitude)::numeric" " -> Sort (cost=2019.51..2037.61 rows=7239 width=16) (actual time=30.704..32.171 rows=2111 loops=1)" "Sort Key: a.lat, a.lon" "Sort Method: quicksort Memory: 212kB" "-> Index Scan using depthidx on a (cost=0.00..1555.43 rows=7239 width=16) (actual time=0.533..24.631 rows=2111 loops=1)" " Index Cond: (depth = 0)" " Filter: ((value > 0::numeric) AND (value < 2::numeric))" " -> Materialize (cost=17871190.21..18083338.96 rows=16971900 width=420) (actual time=751324.751..919278.574 rows=16963350 loops=1)" "-> Sort (cost=17871190.21..17913619.96 rows=16971900 width=420) (actual time=751324.744..820522.604 rows=16963350 loops=1)" " Sort Key: (getwoalatitude((b.latitude)::numeric)), (getwoalongitude((b.longitude)::numeric))" " Sort Method: external merge Disk: 4599344kB" " -> Seq Scan on b (cost=0.00..750696.00 rows=16971900 width=420) (actual time=1.781..229158.949 rows=16971901 loops=1)" "Total runtime: 942295.914 ms" EXPLAIN ANALYZE SELECT b.* FROM b JOIN a ON (getwoalatitude(b.latitude::numeric) = a.lat AND getwoalongitude(b.longitude::numeric) = a.lon) WHERE a.value > 0 AND a.value < 2 AND a.depth = 0 "Merge Join (cost=17873237.91..26594735.94 rows=1984509 width=420) (actual time=755255.801..906407.961 rows=226941 loops=1)" " Merge Cond: ((a.lat = (getwoalatitude((b.latitude)::numeric))) AND (a.lon = (getwoalongitude((b.longitude)::numeric" " -> Sort (cost=2019.51..2037.61 rows=7239 width=16) (actual time=27.658..29.108 rows=2111 loops=1)" "Sort Key: a.lat, a.lon" "Sort Method: quicksort Memory: 212kB" "-> Index Scan using depthidx on a (cost=0.00..1555.43 rows=7239 width=16) (actual time=0.467..21.646 rows=2111 loops=1)" " Index Cond: (depth = 0)" " Filter: ((value > 0::numeric) AND (value < 2::numeric))" " -> Materialize (cost=17871190.21..18083338.96 rows=16971900 width=420) (actual time=753605.414..884549.890 rows=16963350 loops=1)" "-> Sort (cost=17871190.21..17913619.96 rows=16971900 width=420) (actual time=753605.407..822844.299 rows=16963350 loops=1)" " Sort Key
Re: [GENERAL] array datatype supported by Perl DBI with Postgres DBD ?
On Thursday 10 September 2009 18:28:28 Gauthier, Dave wrote: > I have an old O'Reilly "Programming the Perl DBI" and it doesn't mention > supoprt of these things. I suppose that this might be because some DBs > don't support them. But PG does, and I was wondering how it would pass > column data which is an array when using something like > fetchall_arrayref(). Is there a doc for this somewhere? I googled around > for a while and didn't see anything. Best thing to do is look up the documentation on cpan for the version you have. http://search.cpan.org/~turnstep/DBD-Pg-2.15.1/Pg.pm#Array_support Has been there since 2.0.0. /Michael -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY command character set
> set client_encoding = 'utf8'; > copy from stdin/to stdout; What if I want to do this on the server side (because it's much, much faster)? Does COPY use the default encoding of the database? If not, what? If this is a restrictive as it appears, and there are no outstanding enhancements planned in this area, I might be interested in improving this command to allow specifying the encoding and to have it do obvious stuff like recognize UTF lead bytes automatically. At the very least, the documentation needs some work to explain these subtleties. -- Peter Headland Architect Actuate Corporation -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Wednesday, September 09, 2009 19:14 To: Peter Headland Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] COPY command character set "Peter Headland" writes: > The documentation of the COPY command does not state what character > set(s) are recognized or written. I need to import and export UTF-8 > data; how can I do that? set client_encoding = 'utf8'; copy from stdin/to stdout; regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY command character set
"Peter Headland" writes: >> set client_encoding = 'utf8'; >> copy from stdin/to stdout; > What if I want to do this on the server side (because it's much, much > faster)? Does COPY use the default encoding of the database? If not, > what? > If this is a restrictive as it appears, and there are no outstanding > enhancements planned in this area, I might be interested in improving > this command to allow specifying the encoding and to have it do obvious > stuff like recognize UTF lead bytes automatically. At the very least, > the documentation needs some work to explain these subtleties. The COPY command reference page saith Input data is interpreted according to the current client encoding, and output data is encoded in the the current client encoding, even if the data does not pass through the client but is read from or written to a file. Seems clear enough to me. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] query speed question
On 10 Sep 2009, at 19:09, Christopher Condit wrote: Here are both queries (with explain analyze): EXPLAIN ANALYZE SELECT B.* FROM B, (SELECT lat, lon FROM A WHERE value > 0 AND value < 2 AND depth = 0) AS foo WHERE getwoalatitude(B.latitude::numeric) = foo.lat AND getwoalongitude(B.longitude::numeric) = foo.lon after vacuum analyze: "Merge Join (cost=17873237.91..26594735.94 rows=1984509 width=420) (actual time=752983.201..941125.197 rows=226941 loops=1)" " Merge Cond: ((a.lat = (getwoalatitude((b.latitude)::numeric))) AND (a.lon = (getwoalongitude((b.longitude)::numeric" " -> Sort (cost=2019.51..2037.61 rows=7239 width=16) (actual time=30.704..32.171 rows=2111 loops=1)" "Sort Key: a.lat, a.lon" "Sort Method: quicksort Memory: 212kB" "-> Index Scan using depthidx on a (cost=0.00..1555.43 rows=7239 width=16) (actual time=0.533..24.631 rows=2111 loops=1)" " Index Cond: (depth = 0)" " Filter: ((value > 0::numeric) AND (value < 2::numeric))" Here you can see that the estimated row counts are way off compared to the actual number of rows. That means the statistics for that table aren't reliable; either you need to ANALYZE them or you need to increase the statistics size of the columns involved. This part of the query is the quicker part, but it may be possible that the planner wouldn't have picked a merge-join with the remainder if the estimates were closer to reality. You can paste those queries in http://explain-analyze.info/, although you'll need to strip the quotes from the plan (not sure where those get added, psql doesn't add them). " -> Materialize (cost=17871190.21..18083338.96 rows=16971900 width=420) (actual time=751324.751..919278.574 rows=16963350 loops=1)" "-> Sort (cost=17871190.21..17913619.96 rows=16971900 width=420) (actual time=751324.744..820522.604 rows=16963350 loops=1)" " Sort Key: (getwoalatitude((b.latitude)::numeric)), (getwoalongitude((b.longitude)::numeric))" " Sort Method: external merge Disk: 4599344kB" " -> Seq Scan on b (cost=0.00..750696.00 rows=16971900 width=420) (actual time=1.781..229158.949 rows=16971901 loops=1)" "Total runtime: 942295.914 ms" Here's where most time is spent. Firstly, an external merge to disk is going to be slow. I think the setting controlling the amount of memory available for sorting is work_mem, you can try increasing that in the client (set work_mem TO ) before executing your query and see what value is sufficient. The other issue here is the sequential scan on table b. I think that's caused by the planner needing to merge to disk though. EXPLAIN ANALYZE SELECT b.* FROM b JOIN a ON (getwoalatitude (b.latitude::numeric) = a.lat AND getwoalongitude (b.longitude::numeric) = a.lon) WHERE a.value > 0 AND a.value < 2 AND a.depth = 0 "Merge Join (cost=17873237.91..26594735.94 rows=1984509 width=420) (actual time=755255.801..906407.961 rows=226941 loops=1)" " Merge Cond: ((a.lat = (getwoalatitude((b.latitude)::numeric))) AND (a.lon = (getwoalongitude((b.longitude)::numeric" " -> Sort (cost=2019.51..2037.61 rows=7239 width=16) (actual time=27.658..29.108 rows=2111 loops=1)" "Sort Key: a.lat, a.lon" "Sort Method: quicksort Memory: 212kB" "-> Index Scan using depthidx on a (cost=0.00..1555.43 rows=7239 width=16) (actual time=0.467..21.646 rows=2111 loops=1)" " Index Cond: (depth = 0)" " Filter: ((value > 0::numeric) AND (value < 2::numeric))" " -> Materialize (cost=17871190.21..18083338.96 rows=16971900 width=420) (actual time=753605.414..884549.890 rows=16963350 loops=1)" "-> Sort (cost=17871190.21..17913619.96 rows=16971900 width=420) (actual time=753605.407..822844.299 rows=16963350 loops=1)" " Sort Key: (getwoalatitude((b.latitude)::numeric)), (getwoalongitude((b.longitude)::numeric))" " Sort Method: external merge Disk: 4599344kB" " -> Seq Scan on b (cost=0.00..750696.00 rows=16971900 width=420) (actual time=0.095..229888.646 rows=16971901 loops=1)" "Total runtime: 911284.022 ms" This plan is actually very similar to the original query's plan, it has the same problems. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4aa939f812071577543529! -- 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] COPY command character set
> The COPY command reference page saith > >Input data is interpreted according to the current client encoding, >and output data is encoded in the the current client encoding, even >if the data does not pass through the client but is read from or >written to a file. Rats - I read the manual page twice and that didn't register on my feeble consciousness. I suspect that I didn't look beyond the word "client", since I knew I wasn't interested in client behavior and I was speed-reading. On the assumption that I am not uniquely stupid, maybe we could re-phrase this slightly, with a "for example", and add a heading "Localization"? As a general comment, I18N/L10N is a hairy enough topic that it merits its own heading in any commands where it is an issue. How about my suggestion to add a means (extend COPY syntax) to specify encoding explicitly and handle UTF lead bytes - would that be of interest? -- Peter Headland Architect Actuate Corporation -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Thursday, September 10, 2009 10:38 To: Peter Headland Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] COPY command character set "Peter Headland" writes: >> set client_encoding = 'utf8'; >> copy from stdin/to stdout; > What if I want to do this on the server side (because it's much, much > faster)? Does COPY use the default encoding of the database? If not, > what? > If this is a restrictive as it appears, and there are no outstanding > enhancements planned in this area, I might be interested in improving > this command to allow specifying the encoding and to have it do obvious > stuff like recognize UTF lead bytes automatically. At the very least, > the documentation needs some work to explain these subtleties. The COPY command reference page saith Input data is interpreted according to the current client encoding, and output data is encoded in the the current client encoding, even if the data does not pass through the client but is read from or written to a file. Seems clear enough to me. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY command character set
- "Peter Headland" wrote: > > The COPY command reference page saith > > > >Input data is interpreted according to the current client > encoding, > >and output data is encoded in the the current client encoding, > even > >if the data does not pass through the client but is read from or > >written to a file. > > Rats - I read the manual page twice and that didn't register on my > feeble consciousness. I suspect that I didn't look beyond the word > "client", since I knew I wasn't interested in client behavior and I > was > speed-reading. On the assumption that I am not uniquely stupid, maybe > we > could re-phrase this slightly, with a "for example", and add a > heading > "Localization"? > > As a general comment, I18N/L10N is a hairy enough topic that it > merits > its own heading in any commands where it is an issue. > > How about my suggestion to add a means (extend COPY syntax) to > specify > encoding explicitly and handle UTF lead bytes - would that be of > interest? > > -- > Peter Headland > Architect > Actuate Corporation > > > The COPY command reference page saith > > Input data is interpreted according to the current client > encoding, > and output data is encoded in the the current client encoding, > even > if the data does not pass through the client but is read from or > written to a file. > > Seems clear enough to me. > > regards, tom lane Maybe the link might help? http://www.postgresql.org/docs/8.4/interactive/multibyte.html Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Unable to drop a table due to seemingly non-existent dependencies
Hi, I have a situation where trying to drop a table results in: #drop table cdrimporterror_old; NOTICE: default for table cdrimporterror column cdrimporterrorid depends on sequence cdrimporterror_cdrimporterrorid_seq ERROR: cannot drop table cdrimporterror_old because other objects depend on it HINT: Use DROP ... CASCADE to drop the dependent objects too. Now as you can probably imply from the name if this table, this table has been renamed from cdrimporterror to cdrimporterorr_old and I have (or at least thought I had) removed it's dependency on the sequence by changing the tables column type (from bigserial to bingint) and removing the default value. In fact this table no longer has any constraints or indexes either. Where would I look to find what was causing Postgres to still be seeing the sequence as a dependant? Postgres version is PostgreSQL 8.3.5 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42) Thanks, David. -- 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] COPY command character set
"Peter Headland" writes: > How about my suggestion to add a means (extend COPY syntax) to specify > encoding explicitly and handle UTF lead bytes - would that be of > interest? There are no lead bytes in UTF-8, and we make no pretense of handling UTF-16, so I don't think we'd be interested in some hack that cleans up misencoding problems. The idea of overriding client_encoding has been suggested before. I don't remember if it was rejected or is just languishing on the TODO list. I'd be a little worried about sending clients data in an encoding they aren't expecting, but if it only works for I/O to a file it might be okay. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Getting the oid of an anyelement
If I've got a function (sql or plpgsql) that takes anyelement as a param, how do I determine the type name or oid that was actually passed in? I figure there is probably a function for this but darn if I can find it. Specifically, I'm trying to make a function like Oracle's dump that will take anything as input and return the internal representation of it. So I want to determine what type was passed in and call the appropriate send function. SELECT dump(current_date); dump -- Type=date OID=1082 Len=4 Data=(0,0,13,212) -- 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] COPY command character set
> There are no lead bytes in UTF-8 Sorry, sloppy use of terminology. I should have said "UTF signatures" aka the "byte order mark". IOW, the "magic number" bytes commonly found at the front of UTF encoded files: UTF-16 little-endian FF FE UTF-16 big-endian FE FF UTF-8 EF BB BF These tend to be inserted automatically by text editors, so it would be advantageous to have them handled automatically by COPY (at least as an option). Right now, if I edit a UTF-8 file then load it with COPY, I get errors or bad data if the editor chose to add the 3 signature bytes. Whilst UTF-16 is not supported internally, COPY seems to be a legitimate special case, because it is used for migration to/from other tools that may emit or expect UTF-16. ISTR that Postgres uses UCI? If so it would be near-trivial to allow COPY to read and write UTF-16. If done via a syntax extension to COPY (which I think is the most desirable implementation), this would have no adverse effect on any other capability. It also seems sufficiently isolated from sensitive/complex areas of the code that it might make a suitable first project for someone who is interested in becoming a contributor... -- Peter Headland Architect Actuate Corporation -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Thursday, September 10, 2009 11:13 To: Peter Headland Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] COPY command character set "Peter Headland" writes: > How about my suggestion to add a means (extend COPY syntax) to specify > encoding explicitly and handle UTF lead bytes - would that be of > interest? There are no lead bytes in UTF-8, and we make no pretense of handling UTF-16, so I don't think we'd be interested in some hack that cleans up misencoding problems. The idea of overriding client_encoding has been suggested before. I don't remember if it was rejected or is just languishing on the TODO list. I'd be a little worried about sending clients data in an encoding they aren't expecting, but if it only works for I/O to a file it might be okay. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY command character set
> Maybe the link might help? > > http://www.postgresql.org/docs/8.4/interactive/multibyte.html That page is too generic; what would be helpful is a section in the doc for each command that is affected by I18N/L10N considerations, that identifies how that specific command behaves. Now that I have grasped the behavior, I'm more than happy to edit the COPY doc page, if people think that would be helpful/worthwhile. -- Peter Headland Architect Actuate Corporation -Original Message- From: Adrian Klaver [mailto:akla...@comcast.net] Sent: Thursday, September 10, 2009 11:06 To: Peter Headland Cc: pgsql-general@postgresql.org; Tom Lane Subject: Re: [GENERAL] COPY command character set - "Peter Headland" wrote: > > The COPY command reference page saith > > > >Input data is interpreted according to the current client > encoding, > >and output data is encoded in the the current client encoding, > even > >if the data does not pass through the client but is read from or > >written to a file. > > Rats - I read the manual page twice and that didn't register on my > feeble consciousness. I suspect that I didn't look beyond the word > "client", since I knew I wasn't interested in client behavior and I > was > speed-reading. On the assumption that I am not uniquely stupid, maybe > we > could re-phrase this slightly, with a "for example", and add a > heading > "Localization"? > > As a general comment, I18N/L10N is a hairy enough topic that it > merits > its own heading in any commands where it is an issue. > > How about my suggestion to add a means (extend COPY syntax) to > specify > encoding explicitly and handle UTF lead bytes - would that be of > interest? > > -- > Peter Headland > Architect > Actuate Corporation > > > The COPY command reference page saith > > Input data is interpreted according to the current client > encoding, > and output data is encoded in the the current client encoding, > even > if the data does not pass through the client but is read from or > written to a file. > > Seems clear enough to me. > > regards, tom lane Maybe the link might help? http://www.postgresql.org/docs/8.4/interactive/multibyte.html Adrian Klaver akla...@comcast.net -- 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] Getting the oid of an anyelement
Hello please, try to look on function pg_typeof postgres=# CREATE OR REPLACE FUNCTION x(anyelement) RETURNS oid AS $$ SELECT pg_typeof($1)::oid; $$ LANGUAGE sql; CREATE FUNCTION postgres=# select x(10); x 23 (1 row) postgres=# select x(current_date); x -- 1082 (1 row) postgres=# regards Pavel Stehule 2009/9/10 Scott Bailey : > If I've got a function (sql or plpgsql) that takes anyelement as a param, > how do I determine the type name or oid that was actually passed in? I > figure there is probably a function for this but darn if I can find it. > > Specifically, I'm trying to make a function like Oracle's dump that will > take anything as input and return the internal representation of it. So I > want to determine what type was passed in and call the appropriate send > function. > > SELECT dump(current_date); > > dump > -- > Type=date OID=1082 Len=4 Data=(0,0,13,212) > > -- > 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] Unable to drop a table due to seemingly non-existent dependencies
David Brain wrote: > Hi, > > I have a situation where trying to drop a table results in: > > #drop table cdrimporterror_old; > NOTICE: default for table cdrimporterror column cdrimporterrorid > depends on sequence cdrimporterror_cdrimporterrorid_seq > ERROR: cannot drop table cdrimporterror_old because other objects depend on > it > HINT: Use DROP ... CASCADE to drop the dependent objects too. > > Now as you can probably imply from the name if this table, this table > has been renamed from cdrimporterror to cdrimporterorr_old and I have > (or at least thought I had) removed it's dependency on the sequence by > changing the tables column type (from bigserial to bingint) and > removing the default value. In fact this table no longer has any > constraints or indexes either. > > Where would I look to find what was causing Postgres to still be > seeing the sequence as a dependant? There's a dependency-tracking table pg_depend. Assuming you don't want to just drop the sequence and re-create one with the same name you'll probably need to delete the relevant row from there*. You can track the row via object oids. Example from a db here on a table called "summary": SELECT oid,relname FROM pg_class WHERE relname='summary'; oid | relname -+- 4747904 | summary (1 row) SELECT oid,relname FROM pg_class WHERE relname='summary_cid_seq'; oid | relname -+- 4748275 | summary_cid_seq (1 row) SELECT * FROM pg_depend WHERE objid = 4748275; classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype -+-+--++--+-+- 1259 | 4748275 |0 | 2615 | 4747647 | 0 | n 1259 | 4748275 |0 | 1259 | 4747904 | 1 | a (2 rows) Clearly it's the second row you want to delete. * Can't think of a problem doing this, but it will void your warranty. -- Richard Huxton Archonet Ltd -- 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] Unable to drop a table due to seemingly non-existent dependencies
David Brain writes: > I have a situation where trying to drop a table results in: > #drop table cdrimporterror_old; > NOTICE: default for table cdrimporterror column cdrimporterrorid > depends on sequence cdrimporterror_cdrimporterrorid_seq > ERROR: cannot drop table cdrimporterror_old because other objects depend on > it > HINT: Use DROP ... CASCADE to drop the dependent objects too. > Now as you can probably imply from the name if this table, this table > has been renamed from cdrimporterror to cdrimporterorr_old and I have > (or at least thought I had) removed it's dependency on the sequence by > changing the tables column type (from bigserial to bingint) and > removing the default value. The "ownership" link is still there, evidently, and should be switched to the new table. Read up on ALTER SEQUENCE OWNED BY. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting the oid of an anyelement
Scott Bailey writes: > Specifically, I'm trying to make a function like Oracle's dump that will > take anything as input and return the internal representation of it. So > I want to determine what type was passed in and call the appropriate > send function. You would need to write that in C. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting the oid of an anyelement
2009/9/10 Tom Lane : > Scott Bailey writes: >> Specifically, I'm trying to make a function like Oracle's dump that will >> take anything as input and return the internal representation of it. So >> I want to determine what type was passed in and call the appropriate >> send function. > > You would need to write that in C. > You don't need write it. orafce has it. http://archives.postgresql.org/pgsql-committers/2009-02/msg00197.php regards Pavel Stehule > regards, tom lane > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting the oid of an anyelement
please, try to look on function pg_typeof
Thanks Pavel. Just what I needed. But you're too late on the orafce
recommendation. I had already written it by the time you posted. I would
have written it any way though because Tom said I couldn't :)
You would need to write that in C.
Two problems with that Tom. First, and most importantly, I never learned
C. (Otherwise, I'd be helping you guys develop). Second, I've already
determined that I was going to do this and I'm pretty darn hard headed.
So here it is in pl/pgsql.
CREATE OR REPLACE FUNCTION dump(
p_value anyelement
) RETURNS text AS
$$
DECLARE
v_type TEXT;
v_oid INT;
v_data BYTEA;
v_send TEXT;
BEGIN
SELECT t.typname, t.oid, t.typsend::text
INTO v_type, v_oid, v_send
FROM pg_type t
WHERE t.oid = pg_typeof($1);
IF v_send IS NULL OR v_send = '-' THEN
RAISE EXCEPTION 'Found no send function for %', $1;
ELSE
EXECUTE 'SELECT ' || v_send || '(' ||
quote_literal($1) || '::' || v_type || ')'
INTO v_data;
END IF;
RETURN 'Type=' || v_type ||
' OID=' || v_oid ||
' Len=' || length(v_data) ||
' Data=('|| array_to_string(bytes, ',') || ')'
FROM (
SELECT array(
SELECT get_byte(v_data, i)
FROM generate_series(0, length(v_data) - 1) i
) AS bytes
) sub;
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;
-- Usage --
VALUES (dump(100)),
(dump(10.1)),
(dump(random())),
(dump('foo'::text)),
(dump(current_date)),
(dump(current_timestamp));
column1
--
Type=int4 OID=23 Len=4 Data=(0,0,0,100)
Type=numeric OID=1700 Len=12 Data=(0,2,0,0,0,0,0,1,0,10,3,232)
Type=float8 OID=701 Len=8 Data=(63,236,234,4,253,128,0,0)
Type=text OID=25 Len=3 Data=(102,111,111)
Type=date OID=1082 Len=4 Data=(0,0,13,212)
Type=timestamptz OID=1184 Len=8 Data=(0,1,22,61,166,87,22,96)
--
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] Getting the oid of an anyelement
2009/9/10 Scott Bailey :
>> please, try to look on function pg_typeof
>
> Thanks Pavel. Just what I needed. But you're too late on the orafce
> recommendation. I had already written it by the time you posted. I would
> have written it any way though because Tom said I couldn't :)
>
>> You would need to write that in C.
>
> Two problems with that Tom. First, and most importantly, I never learned C.
> (Otherwise, I'd be helping you guys develop). Second, I've already
> determined that I was going to do this and I'm pretty darn hard headed.
>
> So here it is in pl/pgsql.
>
> CREATE OR REPLACE FUNCTION dump(
> p_value anyelement
> ) RETURNS text AS
> $$
> DECLARE
> v_type TEXT;
> v_oid INT;
> v_data BYTEA;
> v_send TEXT;
> BEGIN
> SELECT t.typname, t.oid, t.typsend::text
> INTO v_type, v_oid, v_send
> FROM pg_type t
> WHERE t.oid = pg_typeof($1);
>
> IF v_send IS NULL OR v_send = '-' THEN
> RAISE EXCEPTION 'Found no send function for %', $1;
> ELSE
> EXECUTE 'SELECT ' || v_send || '(' ||
> quote_literal($1) || '::' || v_type || ')'
> INTO v_data;
> END IF;
>
> RETURN 'Type=' || v_type ||
> ' OID=' || v_oid ||
> ' Len=' || length(v_data) ||
> ' Data=(' || array_to_string(bytes, ',') || ')'
> FROM (
> SELECT array(
> SELECT get_byte(v_data, i)
> FROM generate_series(0, length(v_data) - 1) i
> ) AS bytes
> ) sub;
> END;
> $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;
>
this code is correct - if you would to search some on disk.
regards
Pavel Stehule
>
> -- Usage --
> VALUES (dump(100)),
> (dump(10.1)),
> (dump(random())),
> (dump('foo'::text)),
> (dump(current_date)),
> (dump(current_timestamp));
>
> column1
> --
> Type=int4 OID=23 Len=4 Data=(0,0,0,100)
> Type=numeric OID=1700 Len=12 Data=(0,2,0,0,0,0,0,1,0,10,3,232)
> Type=float8 OID=701 Len=8 Data=(63,236,234,4,253,128,0,0)
> Type=text OID=25 Len=3 Data=(102,111,111)
> Type=date OID=1082 Len=4 Data=(0,0,13,212)
> Type=timestamptz OID=1184 Len=8 Data=(0,1,22,61,166,87,22,96)
>
> --
> 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] Unable to drop a table due to seemingly non-existent dependencies
Hi, On Thu, Sep 10, 2009 at 2:55 PM, Tom Lane wrote: > > The "ownership" link is still there, evidently, and should be switched > to the new table. Read up on ALTER SEQUENCE OWNED BY. > > regards, tom lane > Thank you - that was the issue, once the ownership was switched to the new tables I was able to drop the old ones. That's certainly something I've learned about Postgres today. David -- 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] "show all" command crashes server
On 11/09/2009, at 1:09 AM, Richard Huxton wrote: On this one server if I use the command "show all" in psql, phpPgAdmin or pgAdmin3 the postgresql server spits the dummy as follows: postgres=# show all; server closed the connection unexpectedly Hmm - some modules can provide their own config variables. Do you have the same modules installed in all three servers? How can I determine what modules are installed ? I do know that pgmemcache is installed on this server - but it was there before the problems started and it works ok. Can you "show" individual variables? I did a show all on one of the other servers, created a script to use each of the resulting outputs in a single show statement and ran on the problem server. It ran without a fault. I then took the postgresql.conf file from the problem server, grabbed all the config lines and submitted them one at a time (again with a script) and it also worked fine. regards Grant Maxwell -- 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] "show all" command crashes server
Grant Maxwell writes: > On 11/09/2009, at 1:09 AM, Richard Huxton wrote: >> Hmm - some modules can provide their own config variables. Do you have >> the same modules installed in all three servers? > How can I determine what modules are installed ? The contents of the local_preload_libraries and shared_preload_libraries parameters would probably be enough ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY command character set
Peter Headland wrote: > As a general comment, I18N/L10N is a hairy enough topic that it merits > its own heading in any commands where it is an issue. I agree, this seems a good idea because people is often confused by this. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] "show all" command crashes server
On 11/09/2009, at 8:17 AM, Tom Lane wrote: Grant Maxwell writes: On 11/09/2009, at 1:09 AM, Richard Huxton wrote: Hmm - some modules can provide their own config variables. Do you have the same modules installed in all three servers? How can I determine what modules are installed ? The contents of the local_preload_libraries and shared_preload_libraries parameters would probably be enough ... regards, tom lane On the problem server: shared_preload_libraries = 'pgmemcache' #local_preload_libraries = '' on the others both are emply. For good measure I removed pgmemcache but the problem persists. I have now put it back. regards Grant -- 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] "show all" command crashes server
Grant Maxwell writes: > On the problem server: > shared_preload_libraries = 'pgmemcache' > #local_preload_libraries = '' > on the others both are emply. Sounds like a smoking gun to me. > For good measure I removed pgmemcache but the problem persists. Did you restart the postmaster afterwards? shared_preload_libraries is only considered at postmaster start. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] "show all" command crashes server
On 11/09/2009, at 8:36 AM, Tom Lane wrote: Grant Maxwell writes: On the problem server: shared_preload_libraries = 'pgmemcache' #local_preload_libraries = '' on the others both are emply. Sounds like a smoking gun to me. For good measure I removed pgmemcache but the problem persists. Did you restart the postmaster afterwards? shared_preload_libraries is only considered at postmaster start. yep - full restart. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] "show all" command crashes server
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 >> shared_preload_libraries = 'pgmemcache' ... > Sounds like a smoking gun to me. Yep, known problem with pgmemcache. Bruce and I poked around with this about a year ago. Bruce, I think you were going to throw the problem at some EDB people - did that ever happen? I seem to recall we fixed that particular problem as well during the codeathon at OpenSQL Camp. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200909102039 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkqpnIEACgkQvJuQZxSWSsgIPQCgnvLBNKLqeAVcx8r2ufEcNPyF bZ4An2Ed60lQ1kyokrAoGJFPQm1fwpOQ =3i3z -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Open source database design tool , alternative to MicroOLDAP
Hi all. is there available some freeware and/or opensource visual database design tool for postgresql ? Something like commercial microOLAP ? thanx for help -- 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] Open source database design tool , alternative to MicroOLDAP
Check out Power Architect. Pre-major release, it has some bugs, but generally works well. It can also forward and reverse engineer. 2009/9/10 NTPT : > Hi all. is there available some freeware and/or opensource visual database > design tool for postgresql ? Something like commercial microOLAP ? > > thanx for help > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Peter Hunsberger -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] quick survey on schema less database usage
I am an MIT student doing a project on schema-less database usage and would greatly appreciate if you guys can fill out a quick survey on this (should take < 5 mins) http://bit.ly/nosqldb -- View this message in context: http://www.nabble.com/quick-survey-on-schema-less-database-usage-tp25394333p25394333.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
[GENERAL] Getting Out Parameter in the application using libpq
Hi,
I am looking for a way to get the OUT parameters of a FUNCTION/PROCEDURE in
my application (C++) using C libpq library. I can get the result set of an OUT
parameter having REFCURSOR data type through an explicit FETCH ALL from ""
but for OUT parameter of type integer/varchar I dont have a clue. Can anyone
tell me how it is done or suggest any work around for this?
Using the following code I can get the refcursor.
CREATE OR REPLACE
Function getAddresses
(
pName IN varchar2, outCursor refcursor
) RETURN NUMBER
IS
BEGIN
OPEN outCursor FOR SELECT * FROM "dummyTable" WHERE "name"=pName;
return 1;
END getAddresses;
strcat(statement, "SELECT getAddresses('abc', 'outcursor'); FETCH ALL IN
outcursor");
res = PQexec(conn, statement);
if (PQresultStatus(res) != PGRES_TUPLES_OK) {
throw Exception(PQresultErrorMessage(res));
}
cout << "Number of Rows: " << PQntuples(res) << " Number of Columns: "
<< PQnfields(res) << endl;
PQclear(res);
Thanks
Ehsan
Re: [GENERAL] "show all" command crashes server
Grant Maxwell writes: > On 11/09/2009, at 8:36 AM, Tom Lane wrote: >> Did you restart the postmaster afterwards? > yep - full restart. okay, next step is to collect a stack trace ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Installing postgresql on Debian Lenny-->my /etc/apt/sources.list.
Dear my friends, I want to install PostgreSQL in my Debian Lenny box. I can't install PostgreSQL with aptitude: " aptitude install postgresql ". It seems that the aptitude can not find the postgresql. This is my '/etc/apt/sources.list'. I have done "aptitude update" too. And it was completed properly. Please tell me what is my mistake. Thank you very much in advance. === deb http://security.debian.org/ stable/updates main contrib deb ftp://packages.debian.org/debian/ stable main contrib non-free deb-src ftp://packages.debian.org/debian/ stable main contrib non-free deb http://kambing.ui.edu/debian/ stable main contrib non-free deb http://kambing.ui.edu/debian-security/ stable/updates main contrib non-free deb-src http://kambing.ui.edu/debian/ stable main contrib non-free deb-src http://kambing.ui.edu/debian-security/ stable/updates main contrib non-free deb http://security.debian.org/ testing/updates main contrib deb ftp://packages.debian.org/debian/ testing main contrib non-free deb-src ftp://packages.debian.org/debian/ testing main contrib non-free deb http://kambing.ui.edu/debian/ testing main contrib non-free deb http://kambing.ui.edu/debian-security/ testing/updates main contrib non-free deb-src http://kambing.ui.edu/debian/ testing main contrib non-free deb-src http://kambing.ui.edu/debian-security/ testing/updates main contrib non-free -- 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] Installing postgresql on Debian Lenny-->my /etc/apt/sources.list.
On Fri, 2009-09-11 at 13:17 +0700, Ricky Tompu Breaky wrote: > It seems that the aptitude can not find the postgresql. Please post the exact output from the screen. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
