[GENERAL] Foreign Key for PHP serialized data - possible?
Hi,
I have a table1 with a tb1_column that stores PHP serialized data that are
unique integers.
| tb1_column |
---
a:5:{i:0;s:1:"9";i:1;s:2:"5";i:2;s:2:"11";i:3;s:2:"100";i:4;s:2:"10";}
I also have a table2 with unique integers (ids) in tb2_column
| tb2_column | descr |
--
11 | Pears
100 | Plums
9 | Apples
5 | Oranges
10 | Cranberries
What I'm trying to do is to create a foreign key on tb1_column so that if a
number in tb2_column changes or gets deleted, it cascades to the appropriate
segment of the serialized data.
e.g. if an id of Oranges changes from '5' to '24', the tb1_column rows will
get changed in the above example row to:
a:5:{i:0;s:1:"9";i:1;s:2:"24";i:2;s:2:"11";i:3;s:2:"100";i:4;s:2:"10";}
Is this possible? Can anyone suggest a function that would do that?
If not what is the alternative that will have a similar effect?
Thanks
Dave
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Foreign Key for PHP serialized data - possible?
"Hannes Dorbath" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Dave wrote:
>> What I'm trying to do is to create a foreign key on tb1_column so that if
>> a
>> number in tb2_column changes or gets deleted, it cascades to the
>> appropriate
>> segment of the serialized data.
>> e.g. if an id of Oranges changes from '5' to '24', the tb1_column rows
>> will
>> get changed in the above example row to:
>> a:5:{i:0;s:1:"9";i:1;s:2:"24";i:2;s:2:"11";i:3;s:2:"100";i:4;s:2:"10";}
>
> No offense, but that sounds like sick application design. Anyway, a
> plphp trigger can do it:
>
> http://www.commandprompt.com/community/plphp
>
>
> --
> Best regards,
> Hannes Dorbath
> No offense, but that sounds like sick application design
Offense taken :)
Well, you don't know the whole picture, so I can see why this would look bad
to you. That's why asked any other ideas.
The problem I need to solve is this:
I have main table with lets say:
11 | Pears
100 | Plums
9 | Apples
5 | Oranges
10 | Cranberries
Now, I need to create another table with options for each of the above. So
for the apples:
ID | colors_available | favourite_color |
kind | favourite kind | Other similar options ...
9 | red, green, yellow, ...infinity | red | Granny smith, Golden delicious,
... infinity | Granny smith | Other similar values
What I'm trying to do is to keep the options for each froot in one row, as
opposed to creating separate table for colors, kind, etc.
I realize that I could create separate tables for colors, kind, etc, and
separate tables for favourite colors and favourite kind, but this would
involve accessing four different tables in this case. I guess, I can always
set up views with joins, but could not come up with a good way to query this
in a one returned row, e.g.:
ID | Name | colors | fav_col | kind
| fav_kind | etc.
11 | Apples | red, green, etc. | red | Granny smith, Golden
delicious.. | Granny smith | etc.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] Insert/Update to multiple tables with relations
Hi,
I need help with designing a set of queries I am running with psql -f
reports.sql
I have a tb_master with report headers, tb_records table with it's own
serial field and foreign key referencing an "id" of tb_master. The third
tb_details table has two foreign keys referencing the "id"'s of both,
tb_master, and tb_records. Below is a simplistic representation of those
three tables:
CREATE TABLE "sch_reports"."tb_master" (
"id" SERIAL,
"some_ref" VARCHAR
"some_text" VARCHAR
) WITH OIDS;
CREATE TABLE "sch_reports"."tb_records" (
"id" SERIAL,
"master_id" INTEGER NOT NULL,
"some_text" VARCHAR,
CONSTRAINT "fk_master_id" FOREIGN KEY ("master_id")
REFERENCES "sch_reports"."tb_master"("id")
ON DELETE CASCADE
ON UPDATE CASCADE
NOT DEFERRABLE
) WITH OIDS;
CREATE TABLE "sch_reports"."tb_details" (
"master_id" BIGINT NOT NULL,
"record_id" INTEGER NOT NULL,
"some_text" VARCHAR NOT NULL,
CONSTRAINT "fk_record_id" FOREIGN KEY ("record_id")
REFERENCES "sch_reports"."tb_records"("id")
ON DELETE CASCADE
ON UPDATE CASCADE
NOT DEFERRABLE,
CONSTRAINT "fk_master_id" FOREIGN KEY ("master_id")
REFERENCES "sch_reports"."tb_master"("id")
ON DELETE CASCADE
ON UPDATE CASCADE
NOT DEFERRABLE
) WITH OIDS;
During update/insert a single row is inserted in tb_master, representing a
single report.
In the same transactiom I would like to enter multiple rows (report line
items) into tb_records. A new record should get a new id, and foreign key of
the tb_master. If rows with should USE the id of tb_master and update all
matching rows.
In the same transaction I would like to enter multiple rows into tb_details.
A new record will need to have two foreign keys referencing above two tables
serial "id" rows.
I'm using a function to insert/update tb_master and here is what I have so
far, but I'm having trouble getting/setting appropriate row id's to insert
records for the other two table in the same transaction. I realize I can use
some_ref of tb_master to handle the relations, but I'd like to try with
serial id's first. Aslo, I cannot use some_ref for tb_records and tb_details
relationship, as I require serial IDs of tb_records to be incremential, and
not all tb_details rows will exist for each tb_records row:
CREATE OR REPLACE FUNCTION "public"."report_ins_upd" (in_some_text varchar,
in_some_ref varchar) RETURNS "pg_catalog"."void" AS
$body$
BEGIN
LOOP
UPDATE sch_reports.tb_master SET
some_text = in_some_text,
WHERE some_ref=in_some_ref;
IF found THEN
RETURN;
END IF;
BEGIN
INSERT INTO sch_reports.tb_master (
some_ref,
some_text
)
VALUES (
in_some_ref,
in_some_text,
);
RETURN;
EXCEPTION WHEN unique_violation THEN
-- do nothing
END;
END LOOP;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
Thanks!
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] partitioning and postgres,
Hi every body, I'm working on a database which have big tables and one of the tables is expected to grow very fast so we need to use partitioning. the problem is that I can't find a solution to do the partitioning automatically, I mean what postgres docs describe at (5.9. Partitioning) is not suitable because we need database create new tables as needed -plus updating rules and etc-. In this particular table we want to partition by id column. For another database I already created a procedure in plsql which create new inherited tables as needed -for partitioning- but in this database it's not possible to use a similar solution because of existing jpa code and jpa layer itself. Is there any solution to implement partitioning and database create new inherited tables as needed update rules on main table and do other required steps automatically? Any help is appreciated. Best. -- View this message in context: http://www.nabble.com/partitioning-and-postgres%2C-tp15545607p15545607.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Automatic partitioning,
Hi, I'm working on a database which have big tables and one of the tables is expected to grow very fast so we need to use partitioning. the problem is that I can't find a solution to do the partitioning automatically, I mean what postgres docs describe at (5.9. Partitioning) is not suitable because we need database create new tables as needed -plus updating rules and etc-. In this particular table we want to partition by id column. On other side because of existing jpa code it's not so easy to use procedures for making it automatic. For another database I already created a procedure in plsql which create new inherited tables as needed -for partitioning- but in this database it's not possible to use a similar solution. Is there any solution to implement partitioning and database create new inherited tables as needed? Best. -- View this message in context: http://www.nabble.com/Automatic-partitioning%2C-tp15544618p15544618.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] How do I measure user disk usage on Postgresql table?
Lets say I have a Postgresql table where I store uploaded data for all users, identified by user_id column. Is it possible to run a query to measure the storage a user is consuming? Ideally this would be a SELECT query with WHERE clause on user_id and will only return size of the data field(s), rather then the overhead. Any other ideas? Thanks
Re: [GENERAL] How do I measure user disk usage on Postgresql table?
"Richard Huxton" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Dave wrote: >> Lets say I have a Postgresql table where I store uploaded data for >> all users, identified by user_id column. Is it possible to run a >> query to measure the storage a user is consuming? > > Not really. You could get the size of the whole table with > pg_relation_size() and then estimate a user's share by seeing what % of > rows have their user-id. > > If it's the sort of thing you'll be checking constantly, then I'd keep a > summary table up-to-date using triggers instead. > > Oh - if you're going to be asking questions regularly then you'll probably > get more answers if you have a valid email address. > > -- > Richard Huxton > Archonet Ltd > > -- Richard, Thanks for the answer. So what is the proper way of managing user quota on database (apart from having separate table for each)? > Oh - if you're going to be asking questions regularly then you'll probably > get more answers if you have a valid email address. Isn't that asking for spam (and I know the answer to that)? Why would it matter for someone to answer my post based on my email address, if the discussion is going over the groups? One might just as well use: [EMAIL PROTECTED] At least I'm forthcoming by making it obvious that my email address is not real. Just a thought. -- 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] enable_sort optimization problem
tom lane wrote:
Why does it think that only 159 of the 132245 rows in outages will have
join partners in ipinterface? The actual results look like they all do.
It might be worth looking at the pg_stats rows for the join columns to
see if there's something odd about the statistics.
Here are the pg_stats (as of today, I haven't done any analyzes or
vacuums since the night of my first posting) for outages and ipinterface
(I've obscured some addresses in the ipiddr row, and removed iphostname):
schemaname | tablename | attname | null_frac | avg_width |
n_distinct
|
most_common_vals
|
most_common_freqs
|
histogram_bounds
| correlation
+---++-+---++---+---+-+-
public | outages | outageid | 0 | 4
| -1
|
|
|
{201,14775,27621,39600,53231,66043,79629,92779,105267,119744,134644}
|0.390484
public | outages | svclosteventid | 0 | 4
| -0.945011 |
{280277,356772}
|
{0.00067,0.00067}
|
{17842,54830,81745,107030,135793,163240,191819,219223,268449,309128,371234}
|0.390484
public | outages | svcregainedeventid | 0.00033 | 4
| -0.958031 |
{280279}
|
{0.00067}
|
{17844,54856,81787,107063,135821,163381,191856,219405,268450,309572,371494}
|0.390818
public | outages | nodeid | 0 | 4
|396 |
{962,397,191,185,377,139,237,378,1295,231}
|
{0.017,0.0097,0.0087,0.0083,0.0083,0.0077,0.0077,0.0077,0.0077,0.007}
|
{3,158,178,206,236,258,293,316,358,395,1452}
| -0.0325868
public | outages | ipaddr | 0 |16
|396 |
{208,172.22.0.158,172.20.0.237,172.20.0.231,172.22.35.56,172.17.2.5,172.20.0.180,172.21.240.91,172.23.181.16,172.21.240.93}
|
{0.017,0.0097,0.0087,0.0083,0.0083,0.0077,0.0077,0.0077,0.007
[GENERAL] 8.0 and Mandrake 10.1
Hi, When I tried to install postgresql 8.0.0 on my Mandrake 10.1 community box, it puked. First it told me I was missing readline, so I compiled that and got over that hump. I tried --with-openssl and again, the configure stopped when it could not find crypt So, I d'loaded the latest openssl and installed it as per the usual /usr/local/ssl etc... postgresql still refuses to configure for a build with openssl... now, I am no Mandrake expert here, it's my first Mandrake install... but does anyone have a simple explanation for this. My various RH boxes all have no problems with this... postgresql built fine without openssl... but I would like SSL, so I am confused here. Any tips for Mandrake 10.1 and building postgresql most appreciated... Thanks ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] transactions in c api (libpq)?
Is it possible to write C code (presumably using libpq) to carry out transactions? I can't seem to find functions in this API which start and end a transaction. If this is not possible, what other programming interfaces will allow me to write transaction code? Thanks ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Where is the reference manual?
Hi, I see this line in the postgresql manual The Reference Manual gives a more detailed explanation of these catalogs and their attributes. However, The major Postgres system catalogs shows the major entities and their relationships in the system catalogs. but I can't find the reference manual anywhere. Maybe it has been merged somewhere, but I can't find the info I want, which is teh detailed explanation of the system catalogs. Any ideas? thanks Dave
[GENERAL] problem compiling php for pgsql
Hi, Not sure which list this should go to, so I've posted to both. I installed pgsql 7.02, from the RH RPMS, and got pgsql working OK. I then went back to my php-4.0.1pl2 distribution which I'd had working for a while to try and compile in pgsql. tried --with-pgsql (also =/usr, =/usr/local, =/usr/local/pgsql) On compiling php, each time I get /bin/sh /usr/local/php-4.0.1pl2/libtool --silent --mode=compile gcc -DHAVE_CONFIG_H -I. -I/usr/local/php-4.0.1pl2/ext/pgsql -I/usr/local/php-4.0.1pl2 -I/usr/local/php-4.0.1pl2/main -I/usr/local/apache/include -I/usr/local/php-4.0.1pl2/Zend -I/usr/local/php-4.0.1pl2 -I/usr/local/php-4.0.1pl2/ext/mysql/libmysql -I/usr/local/php-4.0.1pl2/ext/xml/expat/xmltok -I/usr/local/php-4.0.1pl2/ext/xml/expat/xmlparse -I -DXML_BYTE_ORDER=12 -g -O2 -c pgsql.c In file included from pgsql.c:29: php_pgsql.h:32: postgres.h: No such file or directory php_pgsql.h:33: libpq-fe.h: No such file or directory php_pgsql.h:39: libpq/libpq-fs.h: No such file or directory So I went back to pgsql to look for these include files. I couldn't find an RPM for them, so I installed postgresql-7.0.2-base source. I did a make install-headers (am I on the wrong track yet). This appeared to install the headers to /usr/local/pgsql/include, although I noticed there was no libpq-fe.h My recompile of pgsql gave the same results (even for postgres.h). I can't believe I'm going about this the right way. Does anyone know what I should be doing? I'd like to try and keep this as much RPM based as possible (i.e. I did think of downloading ALL sources and compiling the lot, but I'd like to avoid that - I mean my pgsql install works fine) many thanks, Dave
[GENERAL] Re: [PHP-INST] problem compiling php for pgsql
Jouni Ahto wrote: > > Either do a *full* install of PostgreSQL, not only headers, from sources > and remove all the RPM's, or, because you seem to prefer them, install > also postgresql-devel-7.0.2-2.i386.rpm, which I think is missing from your > system, remove /usr/local/pgsql, ./configure --with-pgsql=/usr and > recompile. Having bits of postgres installed from RPM's, other bits from > sources to another place is very likely to play havoc on PHP's configure > script. Thanks, yes, it was the devel RPM I needed. Sorry, I should really have spotted that - it was late! Actually, given that the devel RPM puts includes in /usr/include, which can then be used by seperate apps that interface with pgsql, is there not an argument for including headers in the base or server RPM? The devel RPM isn't the first thing that occurs when compiling a different app. (I'm sure there are good reasons!) Thanks for your help. Dave
[GENERAL] How to configure pgsql to store chinese?
Hi all, I have my pgsql 7.0.2 with such configure --enable-locale --enable-multibyte=EUC_TW and I created a db with "createdb -E EUC_TW testdb, however, I got "¦w" instead of "¦W" in chinese display. any idea? Thanks Dave
[GENERAL] Does column header support multibyte character?
Can I use chinese as the column header? Thanks Dave
Re: [GENERAL] ISAM Api for Postgres
Patrick FICHE wrote: > > Hi, > > Has anyone already tried to access PostgreSQL through an ISAM API ? > > Thanks > > Patrick Fiche Well by the the number of responses you got I would say no. Really it is quite a shame for there are lots of people out there with legacy apps that use a somewhat modified ISAM interface that would like to move to a database with the ability to mix ISAM and SQL. ( I am currently in this boat). Now I know with informix if you bought the ISAM package it would read and write their database files directly and you could still use their SQL engine. I wonder what would be involved in writing an ISAM access. I would think that there must be routines in there to traverse the indices and database files and find next index entry and previous index entry. -- Dave Smith Candata Systems Ltd. (416) 493-9020 [EMAIL PROTECTED]
Re: [GENERAL] Problem JDBC, AutoCommit ON and SELECT FOR UPDATE
Can you point me to the source of that quote ? The documentation here http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html does not specify anything about ResultSet closing, or completion ? Short version is that the driver does not hold the lock past the return of the result set, so unfortunately not. Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On 6 March 2015 at 11:27, Philippe EMERIAUD wrote: > Hi all, > We have an application based on DB2 database, We are testing this same > application on PostgreSQL database. By default we are in autocommit on mode. > On DB2 (and Oracle), the query SELECT FOR UPDATE locks the row until the > resultset is closed. On PostgreSQL database (all versions) this same query > never locks (because we are in autocommit on mode). But this is a bad > implementation of JDBC specification : "If a connection is in auto-commit > mode, then all its SQL statements will be executed and committed as > individual transactions.[...] The commit occurs when the statement > completes or the next execute occurs, whichever comes first. In the case of > statements returning a ResultSet object, the statement completes when the > last row of the ResultSet object has been retrieved or the ResultSet object > has been closed." > Is it possible to lock row in autocommit on mode on PostgreSQL database ?
[GENERAL] database split
Greetings, We are migrating a subset of our customers to a new set of servers. This requires that we migrate their data stored in postgresql (v9.1.15, Linux) to a new database. The new database happens to reside in the same cluster, but in the future this may not be the case. We are using a separate tablespace for the new database. Our schema has several tiers of foreign key relationships, and most FKs eventually lead back to a few core tables. Here are the options I am considering: A. Text-processing a dump of the original database, filtering only the rows in which I am interested, while loading the dump into the new database. B. Copying the original database (CREATE DATABASE smalldb WITH TEMPLATE bigdb), then modifying the schema so that each FK constraint now includes ON DELETE CASCADE, then deleting rows from the few core tables while letting postgresql cascade the deletes down the hierarchy. Then, of course, restoring the schema to its original state. C. Generating the myriad SELECTs necessary to export only the required data from every table, then importing those results (either using dblink or COPY FROMs) into the new database that has been populated with schema from the original database. Carefully ordering the imports to avoid missing FKs. There are tradeoffs to each approach, of course. A) does not require me to modify/restore our schema, but will take some effort to build the intermediate processing code (for example: having to account for gotchas where our FK columns are not predictably named). B) would probably require the least amount effort to code up, but I am not certain this will perform well (or even work!). Ordering the imports for C) correctly will be a pain, but generating the SELECTs and INSERTs programmatically is straightforward. Of course, I would love to hear about options D) - Z) as well! Thanks in advance for your input. Dave Owens 541-359-2602 TU Corporate Website <http://t.signauxun.com/link?url=http%3A%2F%2Fwww.teamunify.com%2F__corp__%2F&ukey=agxzfnNpZ25hbHNjcnhyGAsSC1VzZXJQcm9maWxlGICAgOCP-IMLDA&k=b64a8344-b9e5-4e6c-ab3f-782da4a277a6> | Swimming is Our Passion <http://info.teamunify.com/swimming-is-our-passion-whats-your-story> TU Facebook <http://t.signauxun.com/link?url=https%3A%2F%2Fwww.facebook.com%2Fteamunify&ukey=agxzfnNpZ25hbHNjcnhyGAsSC1VzZXJQcm9maWxlGICAgOCP-IMLDA&k=832bda8d-6593-4bad-fa2a-31c8623aa381> | Free OnDeck Mobile Apps <http://t.signauxun.com/link?url=http%3A%2F%2Fwww.teamunify.com%2F__corp__%2Fondeck%2F&ukey=agxzfnNpZ25hbHNjcnhyGAsSC1VzZXJQcm9maWxlGICAgOCP-IMLDA&k=3316db21-b178-4e7b-82d8-f6b37b655825> | TeamUnify Blog <http://t.signauxun.com/link?url=http%3A%2F%2Fblog.teamunify.com%2F&ukey=agxzfnNpZ25hbHNjcnhyGAsSC1VzZXJQcm9maWxlGICAgOCP-IMLDA&k=3ca8e429-69ba-43df-ef87-c59e9ca2f19f>
Re: [GENERAL] database split
> > All that said, my guess is you're doing this to support horizontal > scale-out, which means you'll probably need to do this more than once, and > it'd presumably be nice for you and your customers if this didn't require > downtime. I would look at having a way to create a partial replica using > londiste (or BDR if it'd support it). The trick there is having a way to > identify whether you want each individual row on a replica. If you add some > kind of cluster_id field to every table that makes doing that filtering > pretty easy; IIRC londiste supports that out of the box. > Jim, thank you for your input. The reason for the split is partially for horizontal scale-out (current system is well-provisioned for the near future), partly for localization concerns arising from legacy code. We are under a bit of a deadline to finish this split, so selective replication is not feasible at this time. The tools you mention do look very promising for our needs, however. I decided to do a combination of A and C... - load a schema-only dump from the original db to the new db (filtering out triggers and constraints) - do COPY TO/FROMs (I would have to generate all the WHEREs for Jim's option D anyway, the data I am after is only tiny fraction of the original db...) - restore the filtered constraints/triggers Dave Owens 541-359-2602 TU Corporate Website <http://t.signauxun.com/link?url=http%3A%2F%2Fwww.teamunify.com%2F__corp__%2F&ukey=agxzfnNpZ25hbHNjcnhyGAsSC1VzZXJQcm9maWxlGICAgOCP-IMLDA&k=b64a8344-b9e5-4e6c-ab3f-782da4a277a6> | Swimming is Our Passion <http://info.teamunify.com/swimming-is-our-passion-whats-your-story> TU Facebook <http://t.signauxun.com/link?url=https%3A%2F%2Fwww.facebook.com%2Fteamunify&ukey=agxzfnNpZ25hbHNjcnhyGAsSC1VzZXJQcm9maWxlGICAgOCP-IMLDA&k=832bda8d-6593-4bad-fa2a-31c8623aa381> | Free OnDeck Mobile Apps <http://t.signauxun.com/link?url=http%3A%2F%2Fwww.teamunify.com%2F__corp__%2Fondeck%2F&ukey=agxzfnNpZ25hbHNjcnhyGAsSC1VzZXJQcm9maWxlGICAgOCP-IMLDA&k=3316db21-b178-4e7b-82d8-f6b37b655825> | TeamUnify Blog <http://t.signauxun.com/link?url=http%3A%2F%2Fblog.teamunify.com%2F&ukey=agxzfnNpZ25hbHNjcnhyGAsSC1VzZXJQcm9maWxlGICAgOCP-IMLDA&k=3ca8e429-69ba-43df-ef87-c59e9ca2f19f>
Re: [GENERAL] Errors using JDBC batchUpdate with plpgsql function
The logs from the server would be useful Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On 4 May 2015 at 07:05, Thomas Kellerer wrote: > Hannes Erven schrieb am 04.05.2015 um 12:31: > > Hi, > > > > > >> String sql = "select test_user_result_insert_func(?, ?, ?);"; > > > > You can't call functions via JDBC like that. > > That's perfectly valid - unless the function uses out parameters or ref > cursors. > > I am however unsure about batched statements here. So trying > CallableStatement is definitely worth a shot. > > > > > -- > 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] Errors using JDBC batchUpdate with plpgsql function
So we can consider this matter closed ? CallableStatements are necessary because postgres has to use select to call a function. Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On 4 May 2015 at 18:06, Nanker Phelge wrote: > The database function does not use out parameters or a ref cursor, which > was why I was confused. The Java sample I provided is a simplification of > the built-in default logic of the Spring Batch ItemWriter - I put it into > my own implementation class to help with debugging. The root cause seems to > be the JDBC driver thinking that there should not be results because it is > an update and the 'select' influencing that decision. I tried switching > this ItemWriter implementation to use a CallableStatement, and that did > work, but I wanted to understand why the approach I listed in the original > post didn't work. > > Thanks > > > > On Mon, May 4, 2015 at 6:05 AM, Thomas Kellerer > wrote: > >> Hannes Erven schrieb am 04.05.2015 um 12:31: >> > Hi, >> > >> > >> >> String sql = "select test_user_result_insert_func(?, ?, ?);"; >> > >> > You can't call functions via JDBC like that. >> >> That's perfectly valid - unless the function uses out parameters or ref >> cursors. >> >> I am however unsure about batched statements here. So trying >> CallableStatement is definitely worth a shot. >> >> >> >> >> -- >> 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] Unit tests and foreign key constraints
> > I know some say your unit tests shouldn't touch the DB but the more full > stack tests I have, the better I sleep at night :-)) > Unit tests really should be about testing individual bits of logic. Does a single method do the desired thing, and not the undesired thing... Ideally, your data access layer should be interchangeable, ie: use a real database record in production, but use a mock database record for unit tests. I would consider database access to be an integration test, something that you run periodically but not at every commit. I wondered if anyone else has run into this problem and found a good > strategy to mitigate it. > Shouldn't failling to insert due to FK violation be considered an integration test failure? You may want to beef up your tests to make sure the necessary rows get inserted in the right order. Another option would be to automate the creation of a complete set of test data... using psql to load a dump, or possibly CREATE DATABASE testdbcopy WITH TEMPLATE testdb. Integration tests can be costly to maintain with little ROI, tread carefully... -Dave
[GENERAL] Inserting from multiple processes?
I'm using Postgres 8.4 on RHEL 6 and I need to support inserting data into a table from multiple processes with there occasionally being duplicates from the different processes. Here's a simple example table: CREATE TABLE test (tutc TIMESTAMP WITHOUT TIME ZONE, id INTEGER, value INTEGER, PRIMARY KEY (tutc, id)); If I do the following query from 2 processes, then it's fine: INSERT INTO test (tutc, id, value) SELECT '2015-01-01 01:02:03', 4, 5 WHERE NOT EXISTS (SELECT 1 FROM test WHERE tutc='2015-01-01 01:02:03' AND id=4); But if I put the operation in a transaction, then the second process will block until the transaction of the first is commited (which is fine) but then the insert fails with a "duplicate key value violation". I'm guessing that this is because the transaction is making it so that the SELECT only sees the values from before the transaction of the second process began. Using an "upsert" type of function, like the one shown in the documentation ( see http://www.postgresql.org/docs/8.4/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE ) seems like it might work, but I don't need to support updating and was hoping to not have to use a custom function. So is there some way to catch the unique_violation exception without creating a function? Or some other solution to this? Thanks, Dave
Re: [GENERAL] Inserting from multiple processes?
On Mon, Jun 8, 2015 at 10:15 AM, Dave Johansen wrote: > I'm using Postgres 8.4 on RHEL 6 and I need to support inserting data into > a table from multiple processes with there occasionally being duplicates > from the different processes. Here's a simple example table: > CREATE TABLE test (tutc TIMESTAMP WITHOUT TIME ZONE, id INTEGER, value > INTEGER, PRIMARY KEY (tutc, id)); > If I do the following query from 2 processes, then it's fine: > INSERT INTO test (tutc, id, value) SELECT '2015-01-01 01:02:03', 4, 5 > WHERE NOT EXISTS (SELECT 1 FROM test WHERE tutc='2015-01-01 01:02:03' AND > id=4); > > But if I put the operation in a transaction, then the second process will > block until the transaction of the first is commited (which is fine) but > then the insert fails with a "duplicate key value violation". I'm guessing > that this is because the transaction is making it so that the SELECT only > sees the values from before the transaction of the second process began. > > Using an "upsert" type of function, like the one shown in the > documentation ( see > http://www.postgresql.org/docs/8.4/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE > ) seems like it might work, but I don't need to support updating and was > hoping to not have to use a custom function. So is there some way to catch > the unique_violation exception without creating a function? Or some other > solution to this? > For the sake of documentation, here's the function that I used to accomplish this: CREATE FUNCTION insert_test_no_dup(tutc_ TIMESTAMP WITHOUT TIME ZONE, id_ INTEGER, value_ INTEGER) RETURNS VOID AS $$ BEGIN BEGIN INSERT INTO test(tutc, id, value) VALUES (tutc_, id_, value_); RETURN; EXCEPTION WHEN unique_violation THEN -- do nothing because the record already exists END; END; $$ LANGUAGE plpgsql; Both using a rule and using a trigger had the same issue with transactions. Here's the declarations for documentation: CREATE RULE ignore_duplicate_inserts AS ON INSERT TO test WHERE (EXISTS (SELECT 1 FROM test WHERE tutc=new.tutc AND id=new.id)) DO INSTEAD NOTHING; CREATE FUNCTION tf_insert_test_ignore_duplicates() RETURNS trigger AS $$ DECLARE found BOOLEAN; BEGIN SELECT 1 INTO found FROM test WHERE tutc=new.tutc AND id=new.id; IF found THEN RETURN NULL; ELSE RETURN new; END IF; END; $$ LANGUAGE plpgsql; CREATE TRIGGER insert_test_before BEFORE INSERT ON test FOR EACH ROW EXECUTE PROCEDURE tf_insert_test_ignore_duplicates();
[GENERAL] DB access speeds, App(linux)<->PG(linux) vs App(linux) <->MSSql(Windows)
Hi: I'm trying to get a 10,000 ft understanding of the difference in DB access speeds for two different scenarios... Scenario 1: Apps are on linux. PG DB is on linux (different server than apps) Scenario 2: Apps are on linux. MSSql DB is on Windows (obviously a different server) The apps are typically perl scripts using Perl DBI. I'm thinking that since perl has a DBD driver for PG, that would be faster than going through ODBC to get to the MsSQL DB, but I'm not sure about that. Any insights/guesses ? Thanks in Advance.
Re: [GENERAL] DB access speeds, App(linux)<->PG(linux) vs App(linux) <->MSSql(Windows)
>>1) The same schema, data on both DB servers? Yes, identical >>2) Are they the same apps? Yes, the same. The only diff is the db connect. >>3) What are the apps doing? The vast, vast majority are small queries, often run inside large programming loops. >>If you fire off lots and lots of small querys that hit an index and return >>very fast, then native is the way to go. By this are you implying that there is more overhead using ODBC vs Perl DBI-DBD ? If so, any guess, percentage wise, of how much slower it would be? 50% slower? 2x? Other? Some (who really don't understand DB mechanics, or even know what odbc is) are telling me that there is no difference. I say there is, but this is an intuitive response. I'm trying to tap more experienced people for something more solid. -Original Message- From: Andy Colson [mailto:a...@squeakycode.net] Sent: Wednesday, June 24, 2015 10:10 AM To: Gauthier, Dave; Postgres General Subject: Re: [GENERAL] DB access speeds, App(linux)<->PG(linux) vs App(linux) <->MSSql(Windows) On 6/24/2015 8:44 AM, Gauthier, Dave wrote: > Hi: I'm trying to get a 10,000 ft understanding of the difference in > DB access speeds for two different scenarios... > > Scenario 1: Apps are on linux. PG DB is on linux (different server > than apps) > > Scenario 2: Apps are on linux. MSSql DB is on Windows (obviously a > different server) > > The apps are typically perl scripts using Perl DBI. > > I'm thinking that since perl has a DBD driver for PG, that would be > faster than going through ODBC to get to the MsSQL DB, but I'm not > sure about that. > > Any insights/guesses ? > > Thanks in Advance. > I think it'll depend on your usage. If you fire off one really big analytic sql that chews on a huge table and does stats and counts, then odbc vs native will make no difference. If you fire off lots and lots of small querys that hit an index and return very fast, then native is the way to go. -Andy -- 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] DB access speeds, App(linux)<->PG(linux) vs App(linux) <->MSSql(Windows)
OK, so there is a DBD for sybase that you are leveraging to interface with the MSSql DB. And that would be roughly equivilent to the DBD for PG in terms of performance. I mentioned odbc because I thought that was the common thread to use to bridge perl to the MSSql DB. ANd I saw that has introducing more translation steps (slower). Yes, everything else is a constant between the two (network distance, server size/mem, etc...) I think this answers my question. Thanks everyone. -Original Message- From: Francisco Olarte [mailto:fola...@peoplecall.com] Sent: Wednesday, June 24, 2015 12:50 PM To: Gauthier, Dave Cc: Postgres General Subject: Re: [GENERAL] DB access speeds, App(linux)<->PG(linux) vs App(linux) <->MSSql(Windows) Hi Dave: On Wed, Jun 24, 2015 at 3:44 PM, Gauthier, Dave wrote: > Hi: I'm trying to get a 10,000 ft understanding of the difference in > DB access speeds for two different scenarios... > Scenario 1: Apps are on linux. PG DB is on linux (different server than > apps) > > Scenario 2: Apps are on linux. MSSql DB is on Windows (obviously a > different server) > > The apps are typically perl scripts using Perl DBI. > I'm thinking that since perl has a DBD driver for PG, that would be > faster than going through ODBC to get to the MsSQL DB, but I'm not sure about > that. > Any insights/guesses ? From 10k ft, same speed ( variations due to lack of precision in the definition of the problem are going to dwarf everything else ). I assume apps are the same on both, and same network distance. If you use DBI for both you can discount it. If you use DBI you must use DBD. DBD::Pg is quite efficient in my measurements, but for the windows part I do not know what you are using. I'm using Mssql from linux using freetds ( DBD::Sybase, built with freetds, which is equivalent to DBD::Pg built with libpq ) and it goes at the appropiate speed ( I mean, nothing big is going to be gained, optimization time will be bigger than savings ). You say you use ODBC, but where exactly? You use ODBC on linux going to mssql using ¿which library? and DBD::ODBC, or you use remote odbc and an ODBC driver on windows? In any way, speed differences in your queries may be much bigger than requester speeds, unless you have a very complex path ( dbd::proxy going to a windows perl dbiproxy going with DBD::odbc to local mssql ? ). You should time a couple of your queries in real world condition, and test some loops of null queries ( like select 1 and the mssql equivalent in autocommit and no autocommit mode for both ), either of them can win. Also, someone has already told you that for really fast queries native may make a difference. It may, but you have to define 'non native' better. In very fast queries requester differences may be dwarfed by network roundtrips, and if this is a problem to you, you should look for optimizing the common path firsts, things like how many roundtrips each PROTOCOL needs for the small query and other similar. You should measure before. Requester is not normally going to be your big problem. Francisco Olarte. -- 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] Inserting from multiple processes?
On Tue, Jun 9, 2015 at 8:38 AM, Dave Johansen
wrote:
> On Mon, Jun 8, 2015 at 10:15 AM, Dave Johansen
> For the sake of documentation, here's the function that I used to
> accomplish this:
> CREATE FUNCTION insert_test_no_dup(tutc_ TIMESTAMP WITHOUT TIME ZONE, id_
> INTEGER, value_ INTEGER) RETURNS VOID AS
> $$
> BEGIN
> BEGIN
> INSERT INTO test(tutc, id, value) VALUES (tutc_, id_, value_);
> RETURN;
> EXCEPTION WHEN unique_violation THEN
> -- do nothing because the record already exists
> END;
> END;
> $$
> LANGUAGE plpgsql;
>
It appears that calling "SELECT insert_test_no_dup('2015-01-01', 1, 1)"
cause the XID to increment? I'm not sure if it's only when the exception
happens or all the time, but if there some way to prevent the increment of
XID because it's causing problems with our system:
http://www.postgresql.org/message-id/CAAcYxUer3MA=enxvnowe0osaa8comvxcf6orhp-vuppr56t...@mail.gmail.com
Thanks,
Dave
Re: [GENERAL] Inserting from multiple processes?
On Sat, Jun 27, 2015 at 11:00 AM, Francisco Olarte
wrote:
> Hi Dave:
>
> On Fri, Jun 26, 2015 at 2:59 AM, Dave Johansen
> wrote:
>
> > It appears that calling "SELECT insert_test_no_dup('2015-01-01', 1, 1)"
> > cause the XID to increment? I'm not sure if it's only when the exception
> > happens or all the time, but if there some way to prevent the increment
> of
> > XID because it's causing problems with our system:
> >
> http://www.postgresql.org/message-id/CAAcYxUer3MA=enxvnowe0osaa8comvxcf6orhp-vuppr56t...@mail.gmail.com
>
> I, personally, would expect an START TRANSACTION to burn an XID, they
> are serial, and they need to be allocated to have transaction
> ordering, like the thing which happens with the sequences. I assume
> the server can have some optimizations ( like delaying XID adquisition
> to the first appropiate statement, which I think depends on your
> isolation level ), but I would never expect it to not allocate it
> before an insert, it needs it to be sent to the table, in case it
> succeeds, and has to acquire it beforehand, in case someone needs to
> acquire another xid between the time it starts inserting and the time
> it succeeds or fail. Some internals expert may shed some light, but
> after reading your link it seems your problem is just you do too many
> transactions without a vacuum ( also reading your pointed threas it
> sees you do vacuum fulls, which seems unneeded ) and expecting
> postgres has some kind of magic to avoid burning the xids.
The issue is that the following uses 5 XIDs when I would only expect it to
us 1:
BEGIN;
SELECT insert_test_no_dup('2015-01-01', 1, 1);
SELECT insert_test_no_dup('2015-01-02', 2, 2);
SELECT insert_test_no_dup('2015-01-01', 1, 1);
SELECT insert_test_no_dup('2015-01-02', 2, 2);
SELECT insert_test_no_dup('2015-01-01', 1, 1);
SELECT insert_test_no_dup('2015-01-02', 2, 2);
END;
It appears that the unique violation that is caught and ignored increments
the XID even though I didn't expect that to happen. I agree that our
software was burning XIDs needlessly and Postgres handled this situation as
best as it could. It also sounds like Postgres 9.5 adds features to support
this sort of use more efficiently, but the XID incrementing on the unique
violation seems like it could/should be fixed, if it hasn't been already.
Re: [GENERAL] Problem with pl/python procedure connecting to the internet
In cases like this I normally restart the progresql under strace/truss etc and then wade through the output, it will normally tell me which process was invoked. On 23/08/15 18:49, Tom Lane wrote: > Igor Sosa Mayor writes: >> Adrian Klaver writes: >>> Or more to the point how where they built? >> just installed with postgresql which comes with archlinux. I did not >> built anything. But, really: I dont think the problem is plpython2u o 3: >> 1. I have in my system both and both have the same libraries (in this >>case, geopy); >> 2. other procedures with plpython (2 or 3) which DO NOT CONNECT to the >>internet work perfectly. > Well, that hardly proves that Python code that *does* connect to the net > would work. The possibility that you're using a different Python version > inside Postgres and it's broken for network access is one you should take > very seriously. > > However, what this smells like to me is a permissions problem. I think > you were way too quick to dismiss the idea that SELinux (or something > just like it) is restricting outbound internet connections from Postgres. > It's standard for SELinux to be configured so that network-accessible > daemons like Postgres are locked down harder than the very same code > would be treated when being invoked from the command line --- and network > access would be one of the prime candidates to be disabled by default. > > Have you poked around under /var/log/ to see if the kernel logs anything > when the connection attempt doesn't work? For that matter, have you > checked the postmaster log to see what Postgres logs about it? > > 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] Postgres Dropped DB have recovered files how to restore
Ok, 1) back up the file system now as it is. So you can start over when this doesn't work. 2) create an empty database again with no data, but all of the DDL. 3) figure out the names of the new database files, and copy your old ones in with the same name as the new ones 4) cross your fingers. I have no idea if this will work 5) pg_dump everything and reload it into a new db. Let me know how it goes. Dave Cramer da...@postgresintl.com www.postgresintl.com On 6 June 2016 at 16:57, Adrian Klaver wrote: > On 06/04/2016 01:20 PM, lifetronics wrote: > >> This morning I accidently deleted my database for my OpenERP accounting. I >> did not have a good backup system setup so I was unable to do a system >> restore. i did manage to recover the files the drop command removed but I >> dont know how to get the DB back into postgres? Can anyone help me with >> this. I realy need this db to be restored otherwise I am screwed. >> >> These are the folders I recovered(i think my db was in the \base\19456 >> folder): >> >> C:\Program Files (x86)\OpenERP >> 7.0-20141104-021038\PostgreSQL\data\base\19456\ * >> >> C:\Program Files (x86)\OpenERP >> 7.0-20141104-021038\PostgreSQL\data\base\19456\ * >> >> C:\Program Files (x86)\OpenERP >> 7.0-20141104-021038\PostgreSQL\data\base\311296 >> >> C:\Program Files (x86)\OpenERP >> 7.0-20141104-021038\PostgreSQL\data\global\pg_internal.init\ * >> >> C:\Program Files (x86)\OpenERP >> 7.0-20141104-021038\PostgreSQL\data\pg_stat_tmp\ * >> >> I am running Windows Server 2012 R2 and postgres 9.3. Any help wil be much >> apreciated I am kind of desperate. >> > > This is what a complete $DATADIR for 9.3 looks like on a Linux source > install: > > postgres@killi:/usr/local/pgsql93/data> l > total 140 > drwx-- 16 postgres users 4096 Jan 11 09:06 ./ > drwxr-xr-x 7 root root 4096 Jun 27 2013 ../ > drwx-- 14 postgres users 4096 Jun 4 2015 base/ > drwx-- 2 postgres users 4096 Nov 18 2015 global/ > drwx-- 2 postgres users 4096 Jun 27 2013 pg_clog/ > -rw--- 1 postgres users 4476 Jun 27 2013 pg_hba.conf > -rw--- 1 postgres users 1636 Jun 27 2013 pg_ident.conf > drwxr-xr-x 3 postgres users 36864 Jan 11 08:42 pg_log/ > drwx-- 4 postgres users 4096 Jun 27 2013 pg_multixact/ > drwx-- 2 postgres users 4096 Jan 11 08:42 pg_notify/ > drwx-- 2 postgres users 4096 Jun 27 2013 pg_serial/ > drwx-- 2 postgres users 4096 Jun 27 2013 pg_snapshots/ > drwx-- 2 postgres users 4096 Jan 11 09:06 pg_stat/ > drwx-- 2 postgres users 4096 Jan 11 09:06 pg_stat_tmp/ > drwx-- 2 postgres users 4096 Apr 26 2015 pg_subtrans/ > drwx-- 2 postgres users 4096 Feb 23 2015 pg_tblspc/ > drwx-- 2 postgres users 4096 Jun 27 2013 pg_twophase/ > -rw--- 1 postgres users 4 Jun 27 2013 PG_VERSION > drwx-- 3 postgres users 4096 Jun 4 2015 pg_xlog/ > -rw--- 1 postgres users 20329 Jan 11 09:06 postgresql.conf > -rw--- 1 postgres users63 Jan 11 08:42 postmaster.opts > > > So if what you show is everything then you are short some important > directories. > > >> >> >> >> -- >> View this message in context: >> http://postgresql.nabble.com/Postgres-Dropped-DB-have-recovered-files-how-to-restore-tp5906468.html >> Sent from the PostgreSQL - general mailing list archive at Nabble.com. >> >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Re: [GENERAL] How to retrieve jsonb column through JDBC
Unfortunately JSONB is output in text. So this is the way it is done.
Dave Cramer
da...@postgresintl.com
www.postgresintl.com
On 27 August 2016 at 06:39, Alexander Farber
wrote:
> Hello,
>
> what do you use to retrieve a jsonb column using JDBC?
>
> I have tried
>
> Object last_tiles = rs.getObject("last_tiles");
>
> and the resulting Object seems to be a String.
>
> Then I have called (using Jetty JSON class here):
>
> Object last_tiles = JSON.parse(rs.getString("last_tiles"));
>
> And it seems to work ok... but I am still curious, what is the recommended
> (or maybe planned for future) way for retrieving jsonb data in Java.
>
> Also
>
> List last_tiles = (List) JSON.parse(rs.getString("last_tiles"));
>
> has not work for me even though the string is:
>
> [{"col": 7, "row": 8, "value": 1, "letter": "A"}, {"col": 7, "row": 7,
> "value": 2, "letter": "B"}, {"col": 7, "row": 9, "value": 2, "letter": "C"}]
>
> but it is probably the problem of the Jetty class I am using and not of
> JDBC...
>
> Regards
> Alex
>
>
>
Re: [GENERAL] Slightly OT.
Alexander Staubo wrote: > On 6/1/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote: >> > In the meantime, Cybertec (http://www.postgresql.at/, an Austrian >> > company) just announced a commercial synchronous multimaster >> > replication product based on 2-phase commit. It's expensive, and I > [snip] >> I could be completely cranked but I believe that product is based on >> PgCluster which is horrendously slow. > > Well, dang, that's disappointing. Last I checked, the PGCluster design > was fundamentally unscalable. Multimaster replication generally is - thats why Slony-2 will almost certainly never exist in the form that it was originally imagined. Although I'm not (and never have been) an Oracle user, I've heard that RAC has it's own issues in this area as well. Regards, Dave ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: pl/pgsql debuging, was Re: [GENERAL] debugging C functions
> --- Original Message --- > From: "Pavel Stehule" <[EMAIL PROTECTED]> > To: "David Gardner" <[EMAIL PROTECTED]> > Sent: 05/06/07, 21:01:49 > Subject: Re: pl/pgsql debuging, was Re: [GENERAL] debugging C functions > > 2007/6/5, David Gardner <[EMAIL PROTECTED]>: > > This post got me thinking, is there a similar procedure for PL/pgSQL > > functions? > > > > No. You can debug PL/pgSQL function via debug plugin API. Currently > exists only one debugger, which can do it - Enterprisedb debugger. > Or dev builds of pgAdmin - but you still need the plugin. Regards, Dave ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: pl/pgsql debuging, was Re: [GENERAL] debugging C functions
> --- Original Message --- > From: David Gardner <[EMAIL PROTECTED]> > To: "'Dave Page'" <[EMAIL PROTECTED]> > Sent: 06/06/07, 00:14:52 > Subject: RE: pl/pgsql debuging, was Re: [GENERAL] debugging C functions > > I grabbed the May 10th dev snapshot of pgAdmin3, first a little bit of praise > to the pgAdmin3 team for allowing me to run both pgAdmin3 1.7 and 1.6.2 side > by side. > > However what is the debug plugin API? I looked around in postgresql/contrib, > and PostgreSQL.org. I'm assuming this plugin is something that needs to run > server side? > Yes, but at present it's only available as part of EnterpriseDB. A PostgreSQL release should be available Real Soon Now. Regards, Dave ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: pl/pgsql debuging, was Re: [GENERAL] debugging C functions
David Gardner wrote: As someone who would greatly benefit from this feature, is there something I can do to help out in development/testing of this feature? I have a test server I could install a cvs release of pgsql and know my way around a makefile. Hi David, At the moment it's just a case of us finding some time to pull the appropriate code from EnterpriseDB and rebundle it as a PostgreSQL add on. If you wish to test the debugger client though, you can always download a copy of EnterpriseDB to test against (get the latest 8.2 build). There will be some more changes to the client in the next few days though, so look out for an update to pgAdmin. Regards, Dave. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pgadmin3 1.6.3 problem with geom fields
Pedro Doria Meunier wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi All, I've installed pgadmin3 1.6.3 from rpm, under Fedora 7 It runs ok, *except* when the tables have geometry fields! :O When one tries to open/view the table it takes **forever** to display the table's records! (?) Issue being discussed with Pedro on the pgadmin-support list: http://www.pgadmin.org/archives/pgadmin-support/2007-06/msg00046.php Regards, Dave ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [pgadmin-support] Problem editing tables (geom columns)
Pedro Doria Meunier wrote: > (First of all sorry for cross-posting but I feel this is a matter that > interests all recipients) > Thread on pgadmin support: > http://www.pgadmin.org/archives/pgadmin-support/2007-06/msg00046.php > > Hello Dave, Hi Pedro > This behavior (trying to show the entire geometry field) in Fedora > Core 6, pgAdmin3 1.6.2, doesn't happen... > In that scenario the geom field is simply shown blank. There have been no changes in pgAdmin between 1.6.2 and 1.6.3 that might account for this behavioural change afaict. I imagine it's probably some difference in the platform's grid control between the two Fedora versions. > Nevertheless, if I recall it correctly, proj4, geos, postgis versions, > in the above scenario, were older than the ones I'm using... > So I wonder... could it be that pgadmin's code changed for showing > large fields? > Could one of proj4, geos, postgis code changed that really interferes > with pgadmin3? Unlikely. The data is essentially just text, and pgAdmin treats it as such. > IMHO, regardless of the scenario involved, the output for large geom > fields should be suppressed in table edition... (as seen in the past) > The current behavior hogs way too much processor time. Well, the suppression you saw wasn't us - I would guess it was simply that the grid control on the older Fedora just ignores the longer data. The problem we have trying to suppress it ourselves is that we'd either need to do it on a per row basis (because we only actually look at the data when a row is rendered on screen), or pre-scan all the data in every column before displaying it and make a decision on whether or not we should suppress entire column. The problem with the first idea is that it would be very confusing for the user - we might have some rows that we suppress and thus aren't editable, and others that we don't and therefore can be edited. The second method returns us back towards the <= v1.4 behaviour where we had query runtime + display time in the query tool. It would significantly slow things down :-( What I'm currently thinking is just that we add a tab to the Filter/Sort dialog to allow non-primary key columns to be switched on or off, so you can disable the larger columns before the query is even run. We can make that more convenient by saving those options (as well as the sort/filter options) on a per-table basis. I'd like some more thoughts from some of the other pgAdmin devs before we settle on that though. Regards, Dave ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] How to install Postgresql on MS Vista?
D.J. Heap wrote: On 6/21/07, Lee Keel <[EMAIL PROTECTED]> wrote: The first thing you have to do is disable the User Access Control. No you don't, actually. Just start the installer from an elevated command prompt (Right-click on Command Prompt in the start menu and choose Run As Administrator, change to the installer directory and 'start whatever.msi'). Except the administrator account is disabled by default on Vista. Regards, Dave ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] How to install Postgresql on MS Vista?
D.J. Heap wrote: On 6/22/07, Dave Page <[EMAIL PROTECTED]> wrote: [snip] Except the administrator account is disabled by default on Vista. What do you mean? Exactly what I wrote. By default, the .\Administrator account is disabled on Vista so you cannot login to it, or runas it. It's easy to re-enable from the Users and Groups MMC snapin of course. If you logon as an administrator then by default Vista drops a lot of priviledges, but you can get them back to execute a program that requires them by using the 'Run as administrator' right-click menu item (which is on the right-click menu for most things you can run, but not .msi's which is why you have to run them from an elevated command prompt). AFAIK, all the 'Run as administrator' menu item does is give you back your full admin rights if you are an admin (or else it will ask you for an admin user and password) and then run the program in that context. In any case, using an elevated command prompt has worked fine for me with the postgres 8.2.4 installer on 2 different Vista machines and has worked for other msi's that otherwise fail as well. Does it not work for you? I'm not saying what you suggest won't work, only that you cannot do it on an out-of-the-box install. For the record, I've updated the installer for 8.3 to properly work with UAC. Regards, Dave. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] How to install Postgresql on MS Vista?
Joshua D. Drake wrote: BTW, and this is completely off topic but Vista really doesn't seem nearly as bad as all the geeks would make it out to be. It seems a nice evolutionary step ... although it seems a step toward MacOSX ;) Oh it's certainly got nice eye candy, and is quite easy for the newbie to use, but UAC is a pita (I refer you back to the ECPG update regression check hoohaa). Maybe it's because I use a Mac 50% of the time though... /D ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] How to install Postgresql on MS Vista?
Joshua D. Drake wrote: Dave Page wrote: Except the administrator account is disabled by default on Vista. Huh? What I am confused about, and I don't pretend in anyway to understand Vista but I just bought my wife a new vista machine and the default user (the one created during setup) was an Administrator. *An* administrator, not *the* administrator. Think of it in terms of root being disabled, with your wife having 'sudo ' access. Regards, Dave. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Proposed Feature
> --- Original Message --- > From: Magnus Hagander <[EMAIL PROTECTED]> > To: Naz Gassiep <[EMAIL PROTECTED]> > Sent: 23/06/07, 10:39:01 > Subject: Re: [GENERAL] Proposed Feature > > Naz Gassiep wrote: > > Hey, > > I'm sure that'd be greatly appreciated, most other major servers and > > DBs have a similar feature, and that's what the systray is for, i.e., > > viewing major user-installed services. > > Don't forget that the tray (or taskbar notification area as it's > supposed to be called) is one of the most abused areas of the windows > GUI. I've seen installs where it takes up half the screen. So it's not > *necessarily* a good thing - making it mandatory to have an easy way to > turn it off. And if we don't add any actual *functionality*, it should > absolutely not be enabled by default. Providing it as an option is never > wrong, though. > I have no problem adding such a gizmo to the installer if it works well and has no onerous runtime requirements, but I wouldn't want it to be installed by default. /D ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] AutoVacuum Behaviour Question
Bruce McAlister wrote: > Excuse my PGSQL ignorance, I'm new to PostgreSQL, and waiting for these > PGSQL books to become available: > > http://www.network-theory.co.uk/newtitles.html I'm pretty sure you'll find those are just bound copies of http://www.postgresql.org/docs/8.2/interactive/index.html Those are the only docs/books that have been produced by the PostgreSQL Global Development Group. Regards, Dave ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Create user
Ashish Karalkar wrote: > Hello All, > > I am trying to create a user and i dont understand why it is showing > me any massage even after giving parameter -e to the command. > > command : > > C:\Program Files\PostgreSQL\8.2\bin>createuser ashish -S -d -R -l -P -E > -e -U postgres > Enter password for new role: > Enter it again: > Password: > > The following massage appers, I dont want to display this massage > > CREATE ROLE ashish ENCRYPTED PASSWORD > 'md5e34280fa67e9a7d842044e45f2d5d325' NOSU > PERUSER CREATEDB NOCREATEROLE INHERIT LOGIN; > CREATE ROLE > > is there any option? Remove the -e option. That turns on the SQL echoing. Regards, Dave. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] How does one impolement lists, or arrays, pin pl/pgsql ?
How does one implement a simple, general purpose, assignable array (or list) in pl/pgsql? From what I've found/read, it appears that you can declare static, read-only arrays. I'm guessing (and please correct if I'm wrong), PG does not support modifyable arrays. Rather, one might consider defining a temporary table and using sql to insert/select to/from it. True?
Re: [GENERAL] postgressqlnot support inwindows 2000
> --- Original Message --- > From: Magnus Hagander <[EMAIL PROTECTED]> > To: siva prakash <[EMAIL PROTECTED]>, "'PostgreSQL'" > > Sent: 30/06/07, 13:33:04 > Subject: Re: [GENERAL] postgressqlnot support inwindows 2000 > > siva prakash wrote: > > if i run the setup choose language then go to next button it shows error > > *"Failed to create process: 2!* > > Please don't remove the mailinglist from the CC list, so others can > learn from the answers. > > The error you get indicates that your windows installation is broken. At > that point it tries to execute "msiexec" which is a part of windows > installer and a core piece of windows that's not working. You need to > make sure that it works properly before you can install PostgreSQL. Unless I'm misreading it errors when the Next Button is clicked on the language dialog, which means msiexec has already run once. Siva; did you extract both msi files from the zip file before running the installer? Regards, Dave ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] postgressqlnot support inwindows 2000
Magnus Hagander wrote: Yes, but it was not necessarily launched as "msiexec". If the file was just double-clicked on, the path to msiexec will be fetched from the registry and not the system PATH. That's the only explanation I can find. Not being installed on Windows 2000 is possible iirc - but breaking the path and/or renaming the .exe (and then updating the registry to match) seems like some really contrived breakage!! Siva; did you extract both msi files from the zip file before running the installer? That gives a different error message - it starts msiexec and then msiexec is the one that complains. This error indicates that it can't even find msiexec.exe to run. So it does. I'm sure I've seen that one before though; can't remember where... /D ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Stored Procedure: Copy table from; path = text variable
Richard Huxton wrote: > Charles Pare wrote: >> Wow, it works great >> Thank's for the quick answer > > 12 minutes? I've seen bug-patches turned around quicker than that by Tom > ;-) Yeah, that's really quite disappointing Richard - you need to pull your socks up :-) /D ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Stored Procedure: Copy table from; path = text variable
Richard Huxton wrote: > Dave Page wrote: >> Richard Huxton wrote: >>> Charles Pare wrote: >>>> Wow, it works great >>>> Thank's for the quick answer >>> 12 minutes? I've seen bug-patches turned around quicker than that by Tom >>> ;-) >> >> Yeah, that's really quite disappointing Richard - you need to pull your >> socks up :-) > > Would have been under the 10, but it took me 2 minutes to make love to > my wife. I'm getting faster all the time though :-) > OK, now thats a response I wasn't expecting - and a mental image I could have done without! /D ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] pgpass.conf
Ashish Karalkar wrote: > Hello All, > > I am trying tu run a script to create database from a batch programme > and dont want to supply password everytime. > So i tried to setup pgpass.conf file. > File is kept in user profile/application data > i.e > C:\Documents and Settings\postgres\Application Data\postgresql\pgpass.conf > > file contains: > > localhost:5432 :*:postgres:mypass > localhost:5432:qsweb:qsweb:mypass1 > > Still the batch asks for the password.!!! > I am just not getting why its not reading password from pgpass file. > > can anyone please figure out what is going wrong. Under what user account is the batch file being run? The pgpass.conf file needs to be under *that* user account, which is not necessarily the one that the postgresql server runs under. Regards, Dave ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] pgpass.conf
Ashish Karalkar wrote: > The batch file is run under postgres user, also owner of the pgpass.conf > file is postgres. > As far as my knowledge the permission checking is not done on windows > anyways the owner is same so i dont think there is any problem of > permission > OK - have you tried 127.0.0.1 instead of localhost in the pgpass file? > "The permissions on .pgpass must disallow any access to world or group; > achieve this by the command chmod 0600 ~/.pgpass. If the permissions are > less strict than this, the file will be ignored. (The file permissions > are not currently checked on Microsoft Windows, however.)" > That's referring to the checks we do on *nix to ensure the file is secure enough, rather than whether or not the client program can read it which is what Rod was suggesting I think. The Windows ACL model is sufficiently more complex that doing that check is far harder than it is on *nix, and the Application Data directory should be secure anyway (unless you're using FAT32, but then there's no helping you anyway :-) ). Regards, Dave ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] returns setof rec... simple exampe doesn't work
I've googled this one and tried everything (except the correct solution
of course) until tears are starting to flow. Please help. Complete
example below. 7.4.13 on suse-64 x86
create table mytable (mystring varchar, myreal real);
insert into mytable (mystring,myreal) values ('abc',1.23);
insert into mytable (mystring,myreal) values ('def',4.56);
create type myrec as (mystring varchar, myreal real);
create or replace function myfunc() returns setof myrec as '
declare
crec myrec;
begin
for crec in select * from mytable loop
return next crec;
end loop;
return;
end;
' language 'plpgsql';
stdb=# select myfunc();
ERROR: set-valued function called in context that cannot accept a set
CONTEXT: PL/pgSQL function "myfunc" line 6 at return next
Re: [GENERAL] returns setof rec... simple exampe doesn't work
I'll answer my own question...
select * from myfunc();
(dumb, dumb, dumb)
-dave
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Gauthier, Dave
Sent: Monday, July 09, 2007 4:07 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] returns setof rec... simple exampe doesn't work
I've googled this one and tried everything (except the correct solution
of course) until tears are starting to flow. Please help. Complete
example below. 7.4.13 on suse-64 x86
create table mytable (mystring varchar, myreal real);
insert into mytable (mystring,myreal) values ('abc',1.23);
insert into mytable (mystring,myreal) values ('def',4.56);
create type myrec as (mystring varchar, myreal real);
create or replace function myfunc() returns setof myrec as '
declare
crec myrec;
begin
for crec in select * from mytable loop
return next crec;
end loop;
return;
end;
' language 'plpgsql';
stdb=# select myfunc();
ERROR: set-valued function called in context that cannot accept a set
CONTEXT: PL/pgSQL function "myfunc" line 6 at return next
Re: [GENERAL] PGInstaller Project
> --- Original Message --- > From: "Mitchell Vincent" <[EMAIL PROTECTED]> > To: pgsql-general@postgresql.org > Sent: 20/07/07, 23:24:03 > Subject: [GENERAL] PGInstaller Project > > This project is of great interest to me as I am looking for a > easier-to-deploy database server and I've always loved PostgreSQL. the > PGInstaller project (http://pgfoundry.org/projects/pginstaller/) > looks like what I need but I'm worried that it's abandoned. I see a > beta posted back in April but nothing more since then. > pgInstaller certainly isn't dead - it's the installer for the official windows binary releases of PostgreSQL. Regards, Dave ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] European users mailing list
Hi, As discussed at the first meeting of what will become the European PostgreSQL Users Group after pgDay in Prato, we now have a mailing list setup at [EMAIL PROTECTED] This is for the European users group, so is not really intended as a technical list but as a place to discuss events, advocacy and other topics relevant to our work in Europe. To subscribe, send the word 'subscribe' to [EMAIL PROTECTED] To unsubscribe, send the word 'unsubscribe' Apologies for the delay in setting this up - the list is there now, archives will be sorted early next week. Regards, Dave. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [pgsql-advocacy] European users mailing list
> --- Original Message --- > From: Peter Eisentraut <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > Sent: 29/07/07, 14:37:57 > Subject: Re: [pgsql-advocacy] European users mailing list > > Dave Page wrote: > > As discussed at the first meeting of what will become the European > > PostgreSQL Users Group after pgDay in Prato, we now have a mailing > > list setup at [EMAIL PROTECTED] > > > > This is for the European users group, so is not really intended as a > > technical list but as a place to discuss events, advocacy and other > > topics relevant to our work in Europe. > > If it's not a general list, why did you name it "general"? That will > certainly cause confusion. It sounds like -eu-advocacy might have been > better. Who said it wasn't 'general'? I said it wasn't intended to be technical, but that's because technical issues are rarely region specific. That doesn't mean there aren't a variety of other general topics we might discuss. /D ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [pgsql-advocacy] European users mailing list
Joshua D. Drake wrote: > Peter Eisentraut wrote: >> Dave Page wrote: >>> As discussed at the first meeting of what will become the European >>> PostgreSQL Users Group after pgDay in Prato, we now have a mailing >>> list setup at [EMAIL PROTECTED] >>> >>> This is for the European users group, so is not really intended as a >>> technical list but as a place to discuss events, advocacy and other >>> topics relevant to our work in Europe. >> >> If it's not a general list, why did you name it "general"? That will >> certainly cause confusion. It sounds like -eu-advocacy might have >> been better. > > +1 -eu-advocacy seems to make more sense. With all due repect JD, you were not at the meeting at which this was discussed and are presumably unaware that we discussed more than just 'advocacy'. I expect there to be 3 or 4 -eu lists eventually, one of which may well be -advocacy. But for now, I was asked to arrange a general mailing list, which I have done. Regards, Dave. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] psql 8.2 client vs pg 8.1 server problem
Alban Hertroys wrote: We have psql 8.2 clients on our workstations, while we still have pg 8.1 on our development and production servers. This causes problems like the following: database> \d table ERROR: column i.indisvalid does not exist database> We can log into the server and use the local client of course, but this problem is causing some confusion among our new employees and doesn't exactly inspire them with the reliability we know PostgreSQL has... Is there a better workaround, or may this be a bug even? Install the 8.1 version of psql on your workstations (and symlink it to psql81 or something if necessary). psql doesn't make any promises about backward compatibility - for that, you'll need to use something like pgAdmin (even that may need to be pointed at the older versions of pg_dump etc if you're going to run backups from the workstations). Regards, Dave. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] continue in plpgsql 7.4
How does one effectively doa "continue" in plpgsql v7.4? I believe "continue" is supported in v8, but not 7. At any rate, "continue" gets flagged as a syntax error in the code I'm trying. Lacking "continue", what would you suggest? Thanks -dave
[GENERAL] how do i get num of recs inserted/deleted/updated in plpgsql
Looking to capture the total number of records affected with insert/delete/update from within a plpgsql (v7.4 on linux). Would be nice to have this in an integer. Thanks -dave
Re: [GENERAL] how do i get num of recs inserted/deleted/updated in plpgsql
Perhaps rowcnt integer; ... get diagnostics rowcnt := row_count; ??? From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Gauthier, Dave Sent: Wednesday, August 01, 2007 12:05 PM To: pgsql-general@postgresql.org Subject: [GENERAL] how do i get num of recs inserted/deleted/updated in plpgsql Looking to capture the total number of records affected with insert/delete/update from within a plpgsql (v7.4 on linux). Would be nice to have this in an integer. Thanks -dave
[GENERAL] want to drop corrupt db
After a db restore failed for reasons having to do with lack of disk space, when I try to restart the DB, I get... dfg_suse> postmaster -D /proj/cad_sserv/cellibcmp/daveg/Postgres/DB -i > /proj/cad_sserv/cellibcmp/daveg/Postgres/postmaster.log & [1] 29644 dfg_suse> 2007-08-02 14:17:14 LOG: database system was interrupted while in recovery at 2007-08-02 14:16:21 EDT HINT: This probably means that some data is corrupted and you will have to use the last backup for recovery. 2007-08-02 14:17:14 LOG: checkpoint record is at B/258A7100 2007-08-02 14:17:14 LOG: redo record is at A/A700B9C0; undo record is at 0/0; shutdown FALSE 2007-08-02 14:17:14 LOG: next transaction ID: 64132; next OID: 73638687 2007-08-02 14:17:14 LOG: database system was not properly shut down; automatic recovery in progress 2007-08-02 14:17:14 PANIC: could not fsync control file: Disk quota exceeded 2007-08-02 14:17:14 LOG: startup process (PID 29647) was terminated by signal 6 2007-08-02 14:17:14 LOG: aborting startup due to startup process failure [1]Exit 1postmaster -D /proj/cad_sserv/cellibcmp/daveg/Postgres/DB -i > ... dfg_suse> If the DB is trashed, how can I just delete it and start from scratch? Thanks -dave
Re: [GENERAL] pgTray - win32 tray tool for monitoring PostgreSQL service
Magnus Hagander wrote: > Andrei Kovalevski wrote: >>> Have you done any development yet? >> Yes, you can download and try it. Now it's a single pgtray.exe >> application. I'm going to make an msi installer and add "Autostart" >> option to the menu. > > When you do the installer, please make sure it's compatible with > stackbuilder (http://pgfoundry.org/projects/stackbuilder) so it can use > the new easy-to-install method on windows. There is a setup kit in the stackbuilder CVS which makes it a snap to build a compatible MSI installer. Regards. Dave ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Postgres service startup
M S wrote: > Hi, I'm getting errors in my application which suggest that Postgres has not > completely started > when it has told the Windows Service Control Manager that is has. > > Postgres log file extracts which (I believe) support this are shown below: > > 2007-08-06 10:27:42 LOG: database system was shut down at 2007-08-06 > 09:55:16 GMT Daylight Time > 2007-08-06 10:27:42 LOG: checkpoint record is at 0/29C8BE0 > 2007-08-06 10:27:42 LOG: redo record is at 0/29C8BE0; undo record is at 0/0; > shutdown TRUE > 2007-08-06 10:27:42 LOG: next transaction ID: 0/1881853; next OID: 21761 > 2007-08-06 10:27:42 LOG: next MultiXactId: 46; next MultiXactOffset: 91 > 2007-08-06 10:27:42 LOG: database system is ready > 2007-08-06 10:27:56 FATAL: the database system is starting up > 2007-08-06 10:27:57 FATAL: the database system is starting up > 2007-08-06 10:27:57 FATAL: the database system is starting up > 2007-08-06 10:27:57 FATAL: the database system is starting up > > The fatal errors generated by our app have a later timestamp than the message > "database system is ready". > > Looking in the registry for the service start options, pg_ctl.exe is being > called with the runserver argument (which I can't find documented anywhere > I'm afraid). > I have tried adding a -w switch but it prevented the service from starting. > > Has anyone got any suggestions? This has been fixed for 8.2.5 and 8.3: http://developer.postgresql.org/cvsweb.cgi/pgsql/src/bin/pg_ctl/pg_ctl.c?rev=1.74.2.1;content-type=text%2Fx-cvsweb-markup In the meantime, can you delay yoour app startup, per haps by sleeping for a few seconds in a batch file? Regards, Dave. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Comment for column in view - "legal" or just working?
Harald Armin Massa wrote: > Using PostgreSQL 8.1.8 on Windows. > > I have one named listedi in schema cust; that view has one column "name" > > No I used > > comment on cust.listedi.name <http://cust.listedi.name> is > 'MyDescriptive Name'; > > and the command suceeded. Using \d+ in psql also shows me that comment > (also I find no option within pgadmin to view it :) ) You can in 1.8 - that will show a collection of columns under a view, and allow you to set the comment and default value for each. Regards, Dave ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Running a query from the OS CLI
If I have a DB called "foo" ...and... I want to run "select name from table_a where name like 'john%'" ...and... I want no table header "NAME" in the output ...and... I want to do this as a one-liner from the linux command line ...and... I don't want to have to deal with intermediate files or home-grown programs... Is this possible?
Re: [GENERAL] Running a query from the OS CLI
Yes, that works. The actual sql executes a stored function that returns a set of records. Output to the CLI looks great !!! Thanks to all !!! -dave -Original Message- From: Michael Glaesemann [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 08, 2007 2:43 PM To: Gauthier, Dave Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Running a query from the OS CLI On Aug 8, 2007, at 13:13 , Gauthier, Dave wrote: > If I have a DB called "foo" psql --dbname "foo" > > ...and... > > I want to run "select name from table_a where name like 'john%'" psql --dbname "foo" -c "select name from table_a where name like 'john %'" > > ...and... > > I want no table header "NAME" in the output psql --dbname "foo" -c "select name from table_a where name like 'john %'" -t > > ...and... > > I want to do this as a one-liner from the linux command line check. > > ...and... > > I don't want to have to deal with intermediate files or home-grown > programs... Does that work? Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Running a stored procedure via pgagent, need an examp le
> --- Original Message --- > From: novnov <[EMAIL PROTECTED]> > To: pgsql-general@postgresql.org > Sent: 13/08/07, 17:36:12 > Subject: [GENERAL] Running a stored procedure via pgagent, need an example > > Can someone give me a simple example of the way in which I might be able to > call a stored procedure or query using pgagent? I have never scripted a > postgres .sql etc to run via cron or pgagent and an example of the syntax > would be helpful. In pgagent terms, the Step is set up as SQL, and it's the > definition that I would like an example of. I am guessing that one would > save the routine off to a .sql file, then call via \i? Just enter the SQL to execute in the textbox on the step dialog, e.g. SELECT my_proc(); Regards, Dave ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Running a stored procedure via pgagent, need an examp le
novnov wrote: > Thank you, just had time to test this. > > I wrote a simple sproc that adds a row to a test table. I've created a one > step job > > db = db the sproc is in > kind = sql > def = SELECT proc_test01_insertrow(); > > set to run on each minute of every hour every day, starting today 00:00:01 > > The job, step, and schedule all show as enabled. But it never runs. I've > even restarted postgres...what else could be involved? The normal problems we see are: - pgAgent is connecting to a database other than the one containing the pgagent schema. - The user account that pgAgent runs under doesn't have trust access to the database, or a suitable pgpass file to give it the password. You can also enable debugging on the pgAgent command line which should give more info about whats going on. Regards, Dave ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL vs Firebird feature comparison finished
Tony Caduto wrote: > Other than that I would say PG kicks butt. You're just realising that? :-) > If there is any interest I could also add MySQL 5.0 to the mix as the > third column. I'd be interested to see that. Regards, Dave ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PostgreSQL vs Firebird feature comparison finished
Tony Caduto wrote: > Check it out here: > > http://www.amsoftwaredesign.com/pg_vs_fb Couple of corrections Tony: - You don't necessarily need to stop the postmaster to take a filesystem backup - http://www.postgresql.org/docs/8.2/interactive/continuous-archiving.html#BACKUP-BASE-BACKUP. Obviously that assumes logs will be replayed during recovery. - The native win32 port will run on FAT32, we just prevent the installer from initdb'ing on such a partition. You can do it manually however, but tablespaces won't work. I'm a little puzzled about why you list multi-threaded architecture as a feature - on Windows it's a little more efficient of course, but the multi-process architecture is arguably far more robust, and certainly used to be more portable (I'm not sure that's still the case for platforms we actually care about). Regards, Dave. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] reporting tools
If query development is an important part of what you need to do, consider dbQwikEdit. It's not open or free. But you can get a minimal config for free (I think) and it's pretty cheap copnsidering what it can do. It uses ODBC and can read any DB that ODBC points to (Oracle, MySQL, Postgres, SQLServer, etc...). You can enter your queries in by hand (being the sql savy people we are, that's what we'd do). But there is also a GUI that users can run that'll hand-hold them through building SQL using graphics. Pretty neat. This feature allows users to run "ad-hoc". THe output is just tabular. But you can export to lots of different formats. Just a thought. http://www.dbqwikedit.com/ -dave -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Thomas Kellerer Sent: Thursday, August 23, 2007 10:07 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] reporting tools Ned Lilly wrote on 23.08.2007 15:44: > This is specifically why we released OpenRPT as open source - it's very > lightweight, no Java required. http://sf.net/projects/openrpt I am a Java developer and thus I have no problems in using Java based tools. Especially because I ususally only have a JDBC driver for the databases I use around (especially with Oracle this is *very* nice, because it does not require a full client install, only a single .jar file) But OpenRPT looks quite nice, I'll have a look at it as well. I guess I need to install the whole ODBC "shebang" for that, right :) Thomas ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL vs Firebird feature comparison finished
Alban Hertroys wrote: Joshua D. Drake wrote: I agree with you on the multi-threaded. I think I will add a note saying the the multi-threaded architecture is only advantageous on Windows. And Solaris. I'm not entirely sure what makes multi-threading be advantageous on a specific operating system, but I think FreeBSD should be added to that list as well... They've been bench marking their threading support using multi-threading in MySQL (not for the db, mind you - just for load ;), and it performs really well. I'm not sure I necessarily agree with those two - we have no real proof that a multithreaded architecture would be significantly more efficient than a multi process. It certainly wouldn't be as robust as an error in one backend thread could bring down the entire server. Windows is a special case in this regard. The OS has been designed from the outset as a threaded environment. The important point is not that Windows threads are necessarily any more efficient than their Solaris or FreeBSD counterparts, but that the multi-process architecture is alien to Windows and is inherently slower. Two of the major bottlenecks we have on Windows as a result are backend startup time and shared memory access speed - both of which are significantly slower than on *nix. Regards, Dave ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL vs Firebird feature comparison finished
Alban Hertroys wrote: So actually the remark shouldn't be that "the multi-threaded architecture is only advantageous on Windows", but more like "the multi-process architecture is disadvantageous on Windows and hence a multi-threaded architecture is preferred (on that particular OS)". Yeah - but I'm not sure thats necessarily something that should have a place on a bullet point comparison. Regards, Dave ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL vs Firebird feature comparison finished
> --- Original Message --- > From: Tony Caduto <[EMAIL PROTECTED]> > To: "Joshua D. Drake" <[EMAIL PROTECTED]> > Sent: 25/08/07, 15:36:15 > Subject: Re: [GENERAL] PostgreSQL vs Firebird feature comparison finished > > Hi, > Someone mentioned we should put this in the PostgreSQL wiki. > > Do you guys think that would be beneficial? If so, I don't mind the > work on the list I have done so far going on the wiki. > It would make it a lot easier to add other DBs to the mix. > It's aimed at users, not developers so should go on the community docs section of the website, not the wiki. Regards, Dave ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL vs Firebird feature comparison finished
> --- Original Message --- > From: "Stephen Ince" <[EMAIL PROTECTED]> > To: "Tony Caduto" <[EMAIL PROTECTED]>, "Greg Smith" <[EMAIL PROTECTED]>, > pgsql-general@postgresql.org > Sent: 27/08/07, 17:02:21 > Subject: Re: [GENERAL] PostgreSQL vs Firebird feature comparison finished > > Postgres can't be embedded or serverless. Firebird has the embedded feature. > Most of the databases have this capability (hsqldb, derby,oracle,mysql, > firebird, and db2). Derby and hsqldb are the only free embedded databases > for commercial use. > SQL Server CE is also free for commercial use iirc. Regards, Dave ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL vs Firebird feature comparison finished
> --- Original Message --- > From: "Stephen Ince" <[EMAIL PROTECTED]> > To: "Dave Page" <[EMAIL PROTECTED]> > Sent: 27/08/07, 21:30:06 > Subject: Re: [GENERAL] PostgreSQL vs Firebird feature comparison finished > > Dave, >Thx I will take a look. I was trying to port a postgres schema to a > database that had embedded capability. I could not find any non-commerical > databases that supported triggers, sequences, udf function, and stored > procedure. I as I remembered firebird has pretty weak UDF function > capability(only C/C++) and the name size limitation was a killer. > SQL CE is pretty limited as well - no sequences, triggers or udf's either. It works very well with .Net CF of course and is a very useful datastore on pocket pc device though. . Regards, Dave ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] pgsql Windows installer fixed registry key
> --- Original Message --- > From: Dizzy <[EMAIL PROTECTED]> > To: pgsql-general@postgresql.org > Sent: 27/08/07, 21:12:55 > Subject: [GENERAL] pgsql Windows installer fixed registry key > > The pgsql MSI installer does register a registry key but it's random > everytime > it installs (probably something MSI choses). No it's not random. It uses the product ID which only changes between major versions (ie. 8.2 - 8.3) or between distros (eg. pgInstaller vs. EnterpriseDB Postgres). Regards, Dave ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Windows Download
John K Masters wrote: > I have setup a Postgres server on Debian Etch and successfully connected > to it with various *nix clients but I now have to connect a WinXP > client. On accessing the Postgres site I am directed to a download page, > click on the appropriate link and get automatically directed to a > University of Kent site. No problem as I am on the Kent/Surrey border so > this is probably the nearest site. However, on looking at the U of K > page that has come up I see in the top left hand corner the words "This > page does not exist". Now I could have sworn this page existed else how > could I have known it didn't exist? I've fixed a bug that most likely caused the redirect to the wrong place. > Whilst I can appreciate the funny side of this situation I do need to be > able to install a PGSQL client on a windows machine. At the moment, > whichever 'national flag' I select from the Postgres site, I am > redirected to the Kent University page which doesn't exist. I cannot reproduce that - I get redirected to whichever mirror I click on. Anyone else? Thanks for the report, and sorry for any inconvenience caused. Regards, Dave ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL vs Firebird feature comparison finished
Harald Armin Massa wrote: > > the SQL Server 2005 Express download provides software that > > is suitable for application embedding or lightweight application > development. > > > I never developed more then some queries on SQL Server Express or its > different names. > > But I had to work with some applications which used the various > incarnations of SQL Server Express. And EVERYTIME it was a additionally > installed application on the system. The only "embedding" that I could > recognise was the Installer being triggered from the applications > installer. > > MY idea of an embedded database would be "I link with my > software, so that there is only ONE application". > > Is my understaning of "embedded" wrong or oldschool? SQL CE != SQL Express SQL CE is the embedded database, SQL Express is a limited version of SQL Server. Regards, Dave ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Connecting to PostgreSQL server with Mono using ident authetication
Andreas Tille wrote: On Wed, 5 Sep 2007, Andreas Tille wrote: On Mon, 4 Sep 2007, Albe Laurenz wrote: The best list for this kind of thing is certainly the Npgsql mailing list: http://gborg.postgresql.org/mailman/listinfo/npgsql-general Just subscribed. Well, I looked at the archives of this list and also at http://gborg.postgresql.org/mailman/listinfo/npgsql-hackers There is no posting at the later list for 2007 and only zero to two postings per month on the general list. This frequency is very typical for dead projects. Do you think I'm well advised to use Npsql at all or is this an unreasonable conclusion? Npgsql is very much alive and kicking - you're just looking at the old website. Try: http://pgfoundry.org/projects/npgsql/ Regards, Dave ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] replacing Access/ Approach etc
> --- Original Message --- > From: Zenaan Harkness <[EMAIL PROTECTED]> > To: pgsql-general@postgresql.org > Sent: 08/09/07, 06:03:54 > Subject: [GENERAL] replacing Access/ Approach etc > > Is there a way to achieve this, on windows? Yes, 8.2 will start under a admin account just fine. > Is there a way to achieve this, on gnu/linux? Run under any account other than root. How the db is configured to start at boot is distro-dependent though so you'll need to figure that out and disable it to only run your own instsnce. Regards, Dave ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] tradeoffs for multi-schema or multi-db
Here's the situation... I have 2 different apps that both require a separate shema, or maybe db. There is actually one column in one table of each ot these db/schemas that are in common and a desire to :cross" between them in some cases. For example, an app for keeping track of the census results and another app that keeps track of criminal cases in the justice system. They "shared" field is of course the citizen/defendant. Two different apps that should remain separate, but at times it would be nice to check the legal status of the defendant by looking at the census data. Considering the somewhat rare query that will need to bridge these 2 data sources (dbs or schemas), what are the pros/cons of having 2 schemas in the same DB vs 2 DBs? What if the query is to be committed to a PLpgsql function/procedure? How awkward is it to bridge schemas vs bridging dbs in that form? Thanks for any advise/help. -dave
Re: [GENERAL] Version 8.2.5 for Windows doesn't startup normally after upgrading from 8.2.4
On Tue, 2007-09-18 at 14:58 -0500, Walter Roeland wrote: > 2007-09-18 14:28:36 127.0.0.1 postgres postgres FATAL: database > "postgres" does not exist > > And I have to abort the startup. > > Maybe the next is a hint: > When I had blocked the access to localhost with SSL=ON (using hostnossl > pg_hba.conf) there was a constant complaint (2 times per second) with: > 127.0.0.1 postgres postgres FATAL: no pg_hba.conf entry for host > "127.0.0.1", user "postgres", database "postgres", SSL on > > - > Have I something wrong with the configuration of the service? Prior to 8.2.5, the -w option for pg_ctl was broken which meant that the server would report itself running to the service control manager when in actual fact it was still starting up. This would mean that dependent services such as Slony or pgAgent would fail to start because the server wasn't necessarily accepting connections at that time. The -w option tells pg_ctl to attempt to connect to the server every few seconds until successful - and only then report running status to the service control manager, which will then attempt to start the dependent services. In your case, it seems like the 'postgres' database has been removed which prevents pg_ctl connecting. When you blocked access through pg_hba.conf, the fact that the database didn't exist was masked by the lack of access to even attempt the connection. To fix this, either: - Modify the registry key (having taken a backup first of course) with the pg_ctl command line, removing the -w option. - Recreate the postgres database, and ensure it's accessible. Regards, Dave. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] OK, when 8.3 is coming?
> --- Original Message --- > From: Anton Andreev <[EMAIL PROTECTED]> > To: pgsql-general@postgresql.org > Sent: 29/09/07, 13:33:03 > Subject: [GENERAL] OK, when 8.3 is coming? > > Hi, > > Has someone stated when is Postgresql 8.3 coming? Beta 1 early next week if all goes to plan. Regards, Dave ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Upgrading PG
I'm going to move from v7.4.13 to v8.2.0 (suse-64). I have 2 pre-existing DBs. Do I need to "convert" or port them to v8 in any way after I start up with a v8 postmaster? Thanks -dave
Re: [GENERAL] good sql tutorial
> --- Original Message --- > From: Ron Johnson <[EMAIL PROTECTED]> > To: pgsql-general@postgresql.org > Sent: 04/10/07, 17:22:16 > Subject: Re: [GENERAL] good sql tutorial > > You'll see his SQL, squeal like a girl and then forbid him from ever > touching the database again. I'm just saving you the intermediary > steps. LOL - been there, done that. Also seen what then happens when an incorrectly designed report gets sent to the board and industry regulators! I've also seen finance folks get very good with SQL quite quickly, so only the OP can really say if the pain potential is low enough to justify the time savings. Regards, Dave ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Guideline on use of temporary tables
Question regarding temp tables If I (user=joe) attach and run something that uses a temp table, then I (user=joe again) attach again in another session, will there be 2 distinct temp tables? Or does one user get one temp table per DB? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Richard Huxton Sent: Monday, October 15, 2007 5:32 AM To: Jimmy Choi Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Guideline on use of temporary tables Jimmy Choi wrote: > I would like to use temporary table as a caching mechanism to speed up > queries within the same session. > Is this what temporary table is designed for? Are there caveats that I > should be aware of? Can you think of other better alternatives? It's a very common usage of temporary tables. Another is when loading data that you want to process / split up. Bear in mind that a temporary table is private to a particular backend, so if you had 100 connections all using a temporary table for the same query, that could be 100 copies of the data - not necessarily a performance improvement. Also, temporary tables have real entries in the system-tables, so make sure autovacuum (or your manual vacuums) are scanning pg_class etc often enough. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] pgadmin's pgagent job scheduler
Ow Mun Heng wrote: > Does anyone from this list here uses pgagent from pgadmin? > > it's a job schedular much like cron which is sort of integrated w/ > pgadmin3. Only issue which I've found so far which I don't quite like is > that to be able to use pgagent, I have to also pull in the entire > pgadmin3 incl wxGTK and X. This isn't a good thing on a linux server. > (even though X is never used) > > Anyway.. I've installed it from pgadmin-1.4.3 and it's running fine thus > far w/ the jobs and all. (there are some quirks, but that's another > story). Use 1.6.3's version or even 1.8RC1's - they should be much lighter on the library front. > Thus far, the only serious issue I've seen with it is that for some > reason, I can't or is not able to connect pgadmin running from another > client, connecting to the server, to show the available/scheduled jobs. > (I've already selected the File->options->pgagent jobs options) Make sure you use the same database for the initial connection from pgAdmin (the Maintenance DB on the server dialogue) on the remote machine as pgagent connects to. Regards, Dave. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] pgadmin's pgagent job scheduler
Ow Mun Heng wrote: > I've only have 1.4.3 version for centos. (and for gentoo) > so.. I have to use that version. We have RPMs for later versions on the pgAdmin site - do they not work? >>> Thus far, the only serious issue I've seen with it is that for some >>> reason, I can't or is not able to connect pgadmin running from another >>> client, connecting to the server, to show the available/scheduled jobs. >>> (I've already selected the File->options->pgagent jobs options) >> Make sure you use the same database for the initial connection from >> pgAdmin (the Maintenance DB on the server dialogue) on the remote >> machine as pgagent connects to. > > I've changed the maintenance DB which it connects to to be postgres DB > and not it shows up. Eh.. how come? Is this a feature? I assume you mean 'now' not 'not'? It shows up because the pgAgent schema is in that database and both pgAdmin and pgAgent only look for it in the database to which they initially connect. > Additionally, when I connect as NON-postgres user, I can't get to the > pgagent schema. How do I grant access to it? pgagent schema doesnt' seem > to show up as a table. Hmm, that something that no-ones asked about before. We don't setup things that way by default because there is a *BIG SECURITY RISK*: all SQL job steps will run as the user that pgAgent connects to the server as, and all shell/batch steps will run as the OS user that pgAgent runs as. This means that any jobs created by non-privileged users will generally be run by a different user. You have been warned!! You'd need to do something like this from the query tool in the maintenance DB: CREATE USAGE ON SCHEMA pgagent TO role; GRANT EXECUTE ON FUNCTION pgagent.pga_next_schedule(int4, timestamptz, timestamptz, _bool, _bool, _bool, _bool, _bool) TO role; GRANT EXECUTE ON FUNCTION pgagent.pga_is_leap_year(int2) TO role; GRANT EXECUTE ON FUNCTION pgagent.pga_schedule_trigger() TO role; CRANT EXECUTE ON FUNCTION pgagent.pga_exception_trigger() TO role; And then grant the desired update/delete/select/insert permissions on the tables: pgagent.pga_jobagent pgagent.pga_jobclass pgagent.pga_job pgagent.pga_jobstep pgagent.pga_schedule pgagent.pga_exception pgagent.pga_joblog pgagent.pga_jobsteplog Regards, Dave ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] pgadmin's pgagent job scheduler
Ow Mun Heng wrote: > I see.. and in the docs, I was told to import pgagent.sql into the > postgres DB so I did that. but actually. I want to deploy it to the > target DB, let's call it "MyDB". Should I have imported it to MyDB > instead? No, postgres is normal. You can specify with each job step which database it should run on. That way you can have a single job touching multiple databases. >>> Additionally, when I connect as NON-postgres user, I can't get to the >>> pgagent schema. How do I grant access to it? pgagent schema doesnt' seem >>> to show up as a table. >> Hmm, that something that no-ones asked about before. > > never ask.. never know.. :-) > Okay.. so, now it pgagent connects to the DB as user postgres (as I was > instructed by the docs) and runs as user postgres (which is a risk). How > do I connect as a less priv user? (I have a user, named "operator" which > does menial tasks like connect to the DB, pull data, crunch etc..) > > I most certainly don't want it to run as user postgres. User operator > would be better. Just change the pgAgent connection string to use the operator role, and run the daemon under a similarly non-privileged user account. You'll also need to make sure you've granted appropriate permissions on the schema to allow the operator role to update rows etc (which happens when a job is run to set the next runtime). Regards, Dave ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] looking for some real world performance numbers
snacktime wrote: I'm working through the architecture design for a new product. We have a small group working on this. It's a web app that will be using ruby on rails. The challenge I'm running into is that the latest conventional wisdom seems to be that since obviously databases don't scale on the web, you should just not use them at all. I have a group of otherwise very bright people trying to convince me that a rdbms is not a good place to store relational data because eventually it won't scale. And of course we don't even have version 1 of our product out of the door. I'll admit we do have a very good chance of actually getting tons of traffic, but my position is to use a rdbms for relational data, and then if and when it won't scale any more, deal with it then. So what would really help me is some real world numbers on how postgresql is doing in the wild under pressure. If anyone cares to throw some out I would really appreciate it. I've got a client doing 18M page views/ day and postgresql isn't really sweating. Dave Chris ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit
Magnus Hagander wrote: > Another followup. Been working with Dave on and off today (well, him mostly > on to be honest, me a bit more on and off), and it seems that both our > repros clearly blame the desktop heap, and nothing else. Please use the > desktop heap tool and see if it breaks when the desktop heap usage > approaches 100%: > > http://www.microsoft.com/downloads/details.aspx?familyid=5cfc9b74-97aa-4510-b4b9-b2dc98c8ed8b&displaylang=en > > It'd still be good to know why the difference is so big between your two > systems. Further info on this for the record - on XP Pro (which I'm testing on), the desktop heap size defaults to 512KB for non-interactive sessions and 3072KB for interactive. In testing I find that I can get up to around 46 or so connections when running as a service before desktop heap is exhausted and postgres dies. When running interactively I can get a little over 125 connections before things start dying, however, in that case it's *not* because of dekstop heap, because I can start a second cluster and run 125 connections on each simultaneously. If I run three instances up together, one of them will die as soon as desktop heap gets to 100% usage. So, we seem to be hitting two limits here - the desktop heap, and something else which is cluster-specific. Investigation continues... Regards, Dave ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit
Dave Page wrote: > So, we seem to be hitting two limits here - the desktop heap, and > something else which is cluster-specific. Investigation continues... In further info, I've been testing this with the 8.3b1 release build that we put out with pgInstaller, and a build with all optional dependencies (OpenSSL, Kerberos, gettext, ldap etc) disabled. I'm seeing pretty much the same results with each - roughtly 9.6KB of desktop heap used per connection. In addition, I've tried with standard pgbench runs, as well as a script that just does 'select version()'. Again, no differences were observed. Magnus and I did observe that we're using 1 user object and 4 GDI objects per connection. If anyone happens to know how we might identify those, please shout as so far we've drawn a blank :-( Regards, Dave ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit
Trevor Talbot wrote: > The question is where that's coming from. I wondered if it was > desktop heap originally, but there's no reason it should be using it, > and that seems to be precisely the difference between my system and > the others. Connections here are barely making a dent; at 490 there's > an entire 45KB committed in the service desktop. Hmm, Greg mentioned to me earlier that he was suspicious of SSPI which seems to drag in dependencies on gdi32.dll and user32.dll via secur32.dll. Sure enough, testing with 8.2.5 on XP Pro, I get to 150 connections running as a service having used 97.2 of desktop heap (vs. 45 connections max with 8.3). So we have a pretty serious regression in 8.3. Of course, that still doesn't tally up with what you're seeing on Win2k3. I'll test on there tomorrow. Regards, Dave ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit
Trevor Talbot wrote: > I wrote: > > [ desktop heap usage ] > >> It could be that there's a significant difference between XP and 2003 >> in how that's handled though. I do have an XP SP2 machine here with >> 512MB RAM, and I'll try tests on it as soon as I can free up what it's >> currently occupied with. > > ...yep, under XP I'm using about 3.1KB of the service heap per > connection, which tears through it quite a bit faster. Now to figure > out exactly where it's coming from... That ties up with what I'm seeing - on 8.3 it's about 9.6KB per connection, and I get a little under a third as many connections as 8.2 before it dies. /D ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit
Magnus Hagander wrote: > Could you try a build without SSPI? It should be as simple as removing > the #define ENABLE_SSPI 1 from port/win32.h. I don't think you need to > touch the linker lines at all, actually, so try without first. Nope, doesn't help - still using around 9.7KB per connection. Just to be sure I did remove the link option, and checking with depends see that there are now only delay load references to secur32.dll, nothing direct - as is the case with 8.2 So the only other changes I can think of that might affect things are the VC++ build or the shared memory changes, though I can't see why they would cause problems offhand. I'll go try a mingw build... /D ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit
Dave Page wrote: > So the only other changes I can think of that might affect things are > the VC++ build or the shared memory changes, though I can't see why they > would cause problems offhand. I'll go try a mingw build... mingw build of stock 8.3b1, no configure options specified at all, consumes 3.2KB of desktop heap per connection. So, it's either something we're doing different with the VC++ compile/link options, or it's the VC8 runtimes using more resources. Oh, and I still see the second limitation where it bombs out over about 125 connections, so that isn't build/runtime specific. Shall we take this over to -hackers btw? /D ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit
Magnus Hagander wrote:
> Yeah, it could be that the newer MSVCRT files do something we don't
> like.. Other than that, did we upgrade to a different version of some of
> our dependents?
Most of them - but my test build is without any of them:
our $config = {
asserts=>1, # --enable-cassert
integer_datetimes=>1, # --enable-integer-datetimes
nls=>undef, # --enable-nls=
tcl=>undef, # --with-tls=
perl=>undef,# --with-perl
python=>undef, # --with-python=
krb5=>undef, # --with-krb5=
ldap=>0,# --with-ldap
openssl=>undef, # --with-ssl=
xml=>undef,
xslt=>undef,
iconv=>undef,
zlib=>undef # --with-zlib=
};
> Also, is this the DEBUG or RELEASE build of 8.3?
Both behave similarly.
/D
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
