Re: [GENERAL] How to secure PostgreSQL Data for distribute?
Premsun Choltanwanich wrote: Dear All, I need to distribute my application that use PostgreSQL as database to my customer. But I still have some questions in my mind on database security. I understand that everybody who get my application database will be have a full control permission on my database in case that PostgreSQL already installed on their computer and they are an administrator on PostgreSQL. So that mean data, structure and any ideas contain in database will does not secure on this point. Is my understanding correct? What is the good way to make it all secure? Please advise. If you want to keep your data/schema "secure", you have to keep it at your own place and that is just the first step in making it "secure". Espionage, and that is what basically bothers you, is a complex field of enterprize and there is no "one size fits them all". Your first step is keeping the DB at your site and letting your customers connect to it. This may present a huge problem, but there is no other way. No encryption would work, trust me. Nix. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [JDBC] pg_locks.transaction field type
So basically what needs to be changed is TypeInfoCache.java & Oid.java Alvaro Herrera wrote: On Thu, Aug 18, 2005 at 03:55:43PM +1200, Oliver Jowett wrote: Joseph Shraibman wrote: The column's type is 'xid' which the driver doesn't currently handle, so it gets put into the "wrap it in PGobject" bucket. Is xid a type of number? It's an internal backend type; I'm not familiar with the details. It's an unsigned 4 byte integer. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [JDBC] pg_locks.transaction field type
Joseph Shraibman wrote: The column's type is 'xid' which the driver doesn't currently handle, so it gets put into the "wrap it in PGobject" bucket. Is xid a type of number? It's an internal backend type; I'm not familiar with the details. Perhaps your server should convert instances of PGobject to their string representations before returning them across RMI. That's what I'll do. Are there any other classes besides org.postgresql.util.PGobject that I have to worry about? There are other classes for things like intervals and the geometric types, but they should all be subclasses of PGobject. -O ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [JDBC] pg_locks.transaction field type
Oliver Jowett wrote: Joseph Shraibman wrote: Is it a jdbc bug that is returning the answer as org.postgresql.util.PGobject instead of some kind of Number? The column's type is 'xid' which the driver doesn't currently handle, so it gets put into the "wrap it in PGobject" bucket. Is xid a type of number? I'm not sure what's changed between 7.4 & 8.0 -- did you also change JDBC driver versions? yes Perhaps your server should convert instances of PGobject to their string representations before returning them across RMI. That's what I'll do. Are there any other classes besides org.postgresql.util.PGobject that I have to worry about? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [JDBC] pg_locks.transaction field type
Joseph Shraibman wrote: Is it a jdbc bug that is returning the answer as org.postgresql.util.PGobject instead of some kind of Number? The column's type is 'xid' which the driver doesn't currently handle, so it gets put into the "wrap it in PGobject" bucket. I'm not sure what's changed between 7.4 & 8.0 -- did you also change JDBC driver versions? Perhaps your server should convert instances of PGobject to their string representations before returning them across RMI. -O ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] pg_locks.transaction field type
I have a method in my rmi server that takes a query and returns an Object[][]. I had this query: SELECT (select relname from pg_catalog.pg_class where relfilenode = relation) as relname, * FROM pg_locks; After upgrading from 7.4 to 8.0 I was getting this problem: WARNING: Servlet.service() for servlet jsp threw exception java.rmi.UnmarshalException: error unmarshalling return; nested exception is: java.lang.ClassNotFoundException: org.postgresql.util.PGobject (no security manager: RMI class loader disabled) The problem seems to be the "transaction" field. It looks like a number type, but I can't really tell because "\d pg_catalog" doesn't work in psql. So what's the difference between 7.4.7 and 8.0.3? Is it that 7.4.7 never returned anything in the transaction field? Is it a jdbc bug that is returning the answer as org.postgresql.util.PGobject instead of some kind of Number? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] How to secure PostgreSQL Data for distribute?
Dear All, I need to distribute my application that use PostgreSQL as database to my customer. But I still have some questions in my mind on database security. I understand that everybody who get my application database will be have a full control permission on my database in case that PostgreSQL already installed on their computer and they are an administrator on PostgreSQL. So that mean data, structure and any ideas contain in database will does not secure on this point. Is my understanding correct? What is the good way to make it all secure? Please advise.
Re: [GENERAL] trigger question
[Please copy the mailing list on replies so others can contribute
to and learn from the discussion.]
On Wed, Aug 17, 2005 at 08:17:43PM -0500, Apu Islam wrote:
> However, I still get the error.. here is a sample very trim down version.
> I think the "hour" is the problem child. Anyone can give me some clues
> how to manage the variable substitution and string quoting on this
> trigger.
>
> CREATE FUNCTION cust_call_update() RETURNS TRIGGER AS '
> DECLARE
> hourINT ;
> BEGIN
> SELECT INTO hour date_part(''hour'', NEW.h323connecttime) ;
>
> INSERT INTO customer_stat (ip,connecttime,hour) VALUES
> (NEW.cisconasport, NEW.h323connecttime, date_part("hour",
> NEW.h323connecttime)) ;
>
> RETURN NULL ;
> END ;
> 'LANGUAGE 'plpgsql' ;
You're using a variable name (hour) that's the same as a column
name. When PL/pgSQL prepares the INSERT statement it thinks you
want the variable's value where you have the column name, so you
get an error. Rename the variable hour to something else.
Also, in this example, the first argument to date_part() should be
in single quotes (doubled or escaped since you're already inside a
quoted string), not double quotes.
--
Michael Fuhr
---(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] speeding up a query on a large table
Mike Rylander wrote: On 8/17/05, Manfred Koizar <[EMAIL PROTECTED]> wrote: On Mon, 25 Jul 2005 17:50:55 -0400, Kevin Murphy <[EMAIL PROTECTED]> wrote: and because the number of possible search terms is so large, it would be nice if the entire index could somehow be preloaded into memory and encouraged to stay there. You could try to copy the relevant index file(s) to /dev/null to populate the OS cache ... That actually works fine. When I had big problems with a large GiST index I just used cat to dump it at /dev/null and the OS grabbed it. Of course, that was on linux so YMMV. Thanks, Manfred & Mike. That is a very nice solution. And just for the sake of the archive ... I can find the filename of the relevant index or table file name(s) by finding pg_class.relfilenode where pg_class.relname is the name of the entity, then doing, e.g.: sudo -u postgres find /usr/local/pgsql/data -name "somerelfilenode*". -Kevin Murphy ---(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] Field order
On Wed, Aug 17, 2005 at 17:40:34 -0500, "Jim C. Nasby" <[EMAIL PROTECTED]> wrote: > > Ugh, and here I'd been doing it the other way around. I assume > variable-length stuff should always go last, right? I think it depends on the alignment of the type. Certain text, char and varchar should go at the end. Nullable columns also make a difference since there is a shortcut used for rows that contain nulls. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Field order
Thanks for the comments and help! I'll set column order accordingly.
[GENERAL] test
wtf? my messages are not getting posted Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs ---(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] Field order
On Wed, Aug 17, 2005 at 03:24:58PM -0500, Bruno Wolff III wrote: > On Wed, Aug 17, 2005 at 12:09:12 -0600, > Michael Schmidt <[EMAIL PROTECTED]> wrote: > > I've searched the archives and found this question was asked in 2001 but > > never answered. Does the order of fields in a table make a difference? In > > Paradox (from whence I come), there was some belief that reliability was > > increased if memo (text) fields were placed at the end of the table. If > > field order does make a difference, does the EMS PostgreSQL manager > > "reorder" function physically rearrange the fields? > > > > Thanks for your time in considering this basic question > > Some space can be saved by putting the columns with the largest alignments > first. > The columns are not moved around to do the above, but are kept in the > declared order. Ugh, and here I'd been doing it the other way around. I assume variable-length stuff should always go last, right? This make sense? Variable goes last, always Larger alignment before smaller NOT NULL before nullable -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] COMMIT in ps output
yep. looks like it was the library. thanks, Eugene --- Bruce Momjian wrote: > Tom Lane wrote: > > Dr NoName <[EMAIL PROTECTED]> writes: > > > ... My question is, would > > > postgresql ps string show COMMIT at the end of > > > INSERT/UPDATE even if explicit transactions are > not > > > used? > > > > No. > > My guess is that his interface library is doing it. > > -- > Bruce Momjian| > http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) > 359-1001 > + If your life is a hard drive, | 13 Roberts > Road > + Christ can be your backup.| Newtown > Square, Pennsylvania 19073 > > ---(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 > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Are these normal?
Hi, I am sorry if I have to post this the third term having not recieved any response on the earlier two posts. I had also checked the archive for a possible answer but could get any. I installed a working SSL Certificate on Windows XPSP2 and Fedora Core 4. The PostgreSQL 8.0.3 Documentation states the following in the creation of SSL certificates for SSL connection: "Fill out the information that openssl asks for. Make sure that you enter the local host name as "Common Name" I have two surprises: 1. The Server starts with certificate having Common Name that is entirely different from the local host name on both Windows XP and Fedora. 2. The Server starts with certificate having expired validity on both Windows XP and Fedora. Please, are these normal? Thanks Tope Akinniyi http://www.shepherdhill.biz ---(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: [despammed] [GENERAL] Generating random values.
On 8/17/05, A. Kretschmer <[EMAIL PROTECTED]> wrote: > select substring(md5(random()) from 5 for 15); Thanks everybody, this solution will fullfill my needs... ;) Sincerely, Fernando Lujan ---(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] Schema design question
On Wed, 2005-08-17 at 13:40 -0700, Bill Moseley wrote: > a course > and class share so many columns. ...and > I worry about changing a column type on one table and forgetting to > change it on the other table. Postgres types might help here. You could probably create a type that contains the common columns, and then embed that type where you need it. ---(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] speeding up a query on a large table
On 8/17/05, Manfred Koizar <[EMAIL PROTECTED]> wrote: > On Mon, 25 Jul 2005 17:50:55 -0400, Kevin Murphy > <[EMAIL PROTECTED]> wrote: > > and because the number of possible search terms is so large, it > >would be nice if the entire index could somehow be preloaded into memory > >and encouraged to stay there. > > Postgres does not have such a feature and I wouldn't recommend to mess > around inside Postgres. You could try to copy the relevant index > file(s) to /dev/null to populate the OS cache ... That actually works fine. When I had big problems with a large GiST index I just used cat to dump it at /dev/null and the OS grabbed it. Of course, that was on linux so YMMV. -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---(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] Field order
On Wed, Aug 17, 2005 at 12:09:12 -0600, Michael Schmidt <[EMAIL PROTECTED]> wrote: > I've searched the archives and found this question was asked in 2001 but > never answered. Does the order of fields in a table make a difference? In > Paradox (from whence I come), there was some belief that reliability was > increased if memo (text) fields were placed at the end of the table. If > field order does make a difference, does the EMS PostgreSQL manager "reorder" > function physically rearrange the fields? > > Thanks for your time in considering this basic question Some space can be saved by putting the columns with the largest alignments first. The columns are not moved around to do the above, but are kept in the declared order. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] COMMIT in ps output
Tom Lane wrote: > Dr NoName <[EMAIL PROTECTED]> writes: > > ... My question is, would > > postgresql ps string show COMMIT at the end of > > INSERT/UPDATE even if explicit transactions are not > > used? > > No. My guess is that his interface library is doing it. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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 "ping" the database
Bill Moseley <[EMAIL PROTECTED]> writes: > I'm about to post a bug report on DBD::Pg, but I'm wondering if anyone > here could suggest a better way to implement ping() that doesn't fail > just because Postgresql is not allowing SELECTS. I think you could just send an empty query string and see if anything comes back. Of course, this begs the question of what ping is really supposed to test and when it's supposed to be allowable. The above will not work if in the middle of retrieving a query result, for example. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Finding nearest numeric value
On Wed, Aug 17, 2005 at 11:57:52 -0700, Ron Mayer <[EMAIL PROTECTED]> wrote: > Richard Huxton wrote: > > > >While the other answers all do their job, and in one go too, I'd be > >surprised if you found anything faster than: > > > >SELECT myval FROM mytable WHERE myval > 1234 ORDER BY myval LIMIT 1 > > Really? Aren't most things with ORDER BY O(n*log(n))? No. Index lookups are O(log(n)). And you need to do only a constant number of index lookups (2 or 4 depending on whether the values are unique). ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Adding contrib modules
Jonathan Villa schrieb:
> Thanks... at least know I'm doing to correctly... but I still get the
> errors. I've done everything as it states on the tsearch-V2-intro.html
> page... and then I run
>
> psql ftstest < tsearch2.sql &> fts.out
>
> for testing of course
>
> the fts.out file has things like
>
> ERROR: type "tsvector" does not exist
> ERROR: type "tsquery" does not exist
> ERROR: function lexize("unknown", "unknown") does not exist
>
> I'm totally baffled... I'm running 7.4.8 by the way
I think you running a linux/unix machine?
Have you executed ldconfig or similiar after you installed tsearch2?
Is the tsearch2.so in your library path?
With best regards
--
Mario Günterberg
mattheis. werbeagentur
IT Engineer / Projektleiter
Zillestrasse 105a. D - 10585 Berlin
Tel#49-(0)30 . 34 80 633 - 0
Fax#49-(0)30 . 34 80 633 50
http://www.mattheis-berlin.de
Wenn Sie glauben, dies sei ein großes Problem - warten Sie mal ab,
bis wir versuchen die Lösung zu finden. (Walter Matthau)
signature.asc
Description: OpenPGP digital signature
Re: [GENERAL] speeding up a query on a large table
On Mon, 25 Jul 2005 17:50:55 -0400, Kevin Murphy <[EMAIL PROTECTED]> wrote: > and because the number of possible search terms is so large, it >would be nice if the entire index could somehow be preloaded into memory >and encouraged to stay there. Postgres does not have such a feature and I wouldn't recommend to mess around inside Postgres. You could try to copy the relevant index file(s) to /dev/null to populate the OS cache ... >There are 1.3M unique values in that column. That'd mean that each value occours 10 times on average. In your tests the planner consistently estimates 81, and the real numbers are even higher. Can this be explained by the nature of the data distribution? >and from postgresql.conf: >shared_buffers = 15000 >work_mem = 32768 >Everything else in postgresql.conf is default. Setting effective_cache_size to a sane value wouldn't hurt. I don't know about OS X; does it, like Linux, automatically tune its disk cache or do you have to configure it somewhere? >tagged_genes table: >13,982,464 rows >422,028 pages (although about half of that is the experimental tsvector >column, though!) >The index of the query column (mention) is 226,229 pages (= 1.7 GB?). The average tuples per page ratio seems a bit low, both for the heap (~33) and for the index (~62). If the planner's tuple size estimation of 67 bytes is approximately right, there's a lot of free space in your relations. Try VACUUM FULL and REINDEX or CLUSTER to shrink these files. >create table tagged_genes ( >id bigint NOT NULL PRIMARY KEY, -- artificial primary key >mention text, -- a gene name or description >pmid bigint, -- identifies the document that >the mention occurs in >create_date timestamp NOT NULL, >vector tsvector -- experimental tsearch2 index of >mention column >); >create index tg_mention_idx on tagged_genes(mention); >create index tg_pmid_idxon tagged_genes(pmid); >create index tg_vector_idx on tagged_genes(vector); If mention is long (which is not implied by your examples, but an int is still smaller than any nonempty text) and there are many duplicates, it might pay off to put them in their own table: CREATE TABLE mentions ( id SERIAL PRIMARY KEY, mention text UNIQUE, vector tsvector -- experimental tsearch2 index ) WITHOUT oids; create index me_vector_idx on mentions(vector); and reference them from tagged_genes: create table tagged_genes ( id bigint NOT NULL PRIMARY KEY, mentionidint REFERENCES mentions, pmid bigint, -- identifies the document that -- the mention occurs in create_date timestamp NOT NULL ) WITHOUT oids; Unless my math is wrong, this would result in a heap size of ~120K pages and an index size of ~52K pages, plus some 10% slack for updated and deleted tuples, if you VACUUM regularly. Servus Manfred ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] postgres 8.x on debian
On Wed, Aug 17, 2005 at 09:48:35PM +0200, Mario Guenterberg wrote: > marcelo Cortez schrieb: > > Hello all, > > > > does anyone know, whether there is a > > Debian Package for postgresql 8.x for stable version? > > Thanks in advance. > > There is no original package for postgresql v 8.x for debian sarge/stable. > The only original package for this postgresql version is in unstable/sid. > You can try download the source from a debian mirror near you and build > a package for your debian installation. Skip the compiling, just install it and it's dependancies from testing. At this stage of the game there's so little difference between stable and unstable that it'll just work. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpKulR8gI51C.pgp Description: PGP signature
Re: [GENERAL] How to "ping" the database
On Wed, Aug 17, 2005 at 04:25:48PM -0400, Tom Lane wrote: > Of course, this begs the question of what ping is really supposed to > test and when it's supposed to be allowable. The above will not work > if in the middle of retrieving a query result, for example. Well, there's that. I'm not really sure why there's a need for a ping -- and I've heard others question it, too. Perl's DBI has a connect_cached() function that is suppose to return a cached connection if it's still alive. So that is one place "ping" is used. If ping fails then a new connection is created. -- Bill Moseley [EMAIL PROTECTED] ---(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] PostgreSQL 8.0.3 limiting max_connections to 64 ?
eu <[EMAIL PROTECTED]> writes: > max_connections, shared_buffers, shmmax were tweaked, but the server > does not seems to respect the 500 max_connections... Er ... did you restart the postmaster after increasing those parameters? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] postgres 8.x on debian
Mario Guenterberg schrieb: > marcelo Cortez schrieb: > >>Hello all, >> >>does anyone know, whether there is a >>Debian Package for postgresql 8.x for stable version? >>Thanks in advance. > > > There is no original package for postgresql v 8.x for debian sarge/stable. > The only original package for this postgresql version is in unstable/sid. > You can try download the source from a debian mirror near you and build > a package for your debian installation. > Maybe, the compiling of original source works fine under debian stable. > No errors, no missing dependencies or else. > > With best regards > And testing of course. ;-) With best regards -- Mario Günterberg mattheis. werbeagentur IT Engineer / Projektleiter Zillestrasse 105a. D - 10585 Berlin Tel#49-(0)30 . 34 80 633 - 0 Fax#49-(0)30 . 34 80 633 50 http://www.mattheis-berlin.de Wenn Sie glauben, dies sei ein großes Problem - warten Sie mal ab, bis wir versuchen die Lösung zu finden. (Walter Matthau) signature.asc Description: OpenPGP digital signature
Re: [GENERAL] Schema design question
On Wed, Aug 17, 2005 at 07:41:20PM +, Matt Miller wrote: Thanks for responding, Matt: > create table course (id serial primary key, > description varchar); > create table teacher (id serial primary key, > name varchar); > create table course_teacher (course_id integer not null, > teacher_id integer not null); [...] > create table class (id serial primary key, > course_id integer not null, > teacher_id integer not null, > starts_on date, > location varchar); There may be more than one teacher in the class so instead I'd need another "class_teacher" link table. I guess what "bugged" me about this type of layout is that a course and class share so many columns. Duplication just looks wrong -- and I worry about changing a column type on one table and forgetting to change it on the other table. Also have to remember to copy all columns every time a specific class is created. On the other hand, if I used a single table to represent both types of entities, then selects are always going to have something like WHERE type = 'course' added onto the WHERE. That's extra processing for no good reason. > I'm sure there are many ways to get there. To me, the way I've > described is the most-direct way to represent the relationships you've > described. And thanks very much for you help. -- Bill Moseley [EMAIL PROTECTED] ---(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] COMMIT in ps output
Dr NoName <[EMAIL PROTECTED]> writes: > ... My question is, would > postgresql ps string show COMMIT at the end of > INSERT/UPDATE even if explicit transactions are not > used? No. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Schema design question
Bill Moseley schrieb: >On Wed, Aug 17, 2005 at 10:05:39PM +0200, Sebastian Hennebrueder wrote: > > >>> Con: Column duplication in the two tables -- two tables look a lot alike >>> Need to have duplicate link tables (one pointing to each table) >>> >>> >>> >>> >>They are not duplicated. As you say later in your explanation the course >>settings may change. So the data may be the same right at the beginning >>but can differ by the time. => It is not the same data!! >> >> > >I meant that I would have two tables that look like they hold very >similar data. That's not really a problem -- just bugs me to see >duplication. Plus, it could introduce errors if I ever modified, say, >a columns type in one table and not the matching column in the other >table. > >Any opinions on which table layout you would use? > >Thanks, > > > It is still not the same data. When my name is Sebastian Hennebrueder and your name is Sebastian Hennebrueder, than we are not the same person. Class and Course is not the same, they only have accidentally the same data right at the beginning. And as they have a reference to each other you should put them in two separate tables, so that you can create a foreign key relation to enforce the relation. I do not now a good online tutorial for database normalisation but just try Google or ask here. -- Best Regards / Viele Grüße Sebastian Hennebrueder http://www.laliluna.de Tutorials for JSP, JavaServer Faces, Struts, Hibernate and EJB Get support, education and consulting for these technologies - uncomplicated and cheap. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Schema design question
On Wed, 2005-08-17 at 10:49 -0700, Bill Moseley wrote: > The parent object is a general course description, and the > child object is a specific instance of a course > ... > tables that represent classes taught at a > school. The parent object is a general course ... the > child object is ... a "class" -- which > is a course taught at a given time and location. A course can be > taught multiple times ... A course (and thus a class) can have > multiple instructors > > How would you layout the tables for somethings like this? create table course (id serial primary key, description varchar); create table teacher (id serial primary key, name varchar); create table course_teacher (course_id integer not null, teacher_id integer not null); alter table course_teacher add primary key (course_id, teacher_id); alter table course_teacher add foreign key (course_id) references course deferrable initially deferred; create index course_teacher_teacher_ix on course_teacher (teacher_id); alter table course_teacher add foreign key (teacher_id) references teacher deferrable initially deferred; create table class (id serial primary key, course_id integer not null, teacher_id integer not null, starts_on date, location varchar); create index class_course_ix on class (course_id); alter table class add foreign key (course_id) references course deferrable initially deferred; create index class_teacher_ix on class (teacher_id); alter table class add foreign key (teacher_id) references teacher deferrable initially deferred; > A class > normally uses the course's default instructors, but may be different > for specific classes instance. When a class is created the user first specifies course_id. At that point the app can look at course_teacher and offer the list of default teachers. In case a non-default teacher is desired the app also offers a lookup into teacher to see all available teachers. The teacher_id column of class is thus populated. Set the "start_on" date and the "location," and you're done. > I can think (out loud) of three ways to set this up: I'm sure there are many ways to get there. To me, the way I've described is the most-direct way to represent the relationships you've described. ---(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] PostgreSQL 8.0.3 limiting max_connections to 64 ?
max_connections, shared_buffers, shmmax were tweaked, but the server does not seems to respect the 500 max_connections... i *know* i'm missing something obvious, but, what could be wrong ?... i'm lost... any help would be most appreciated... please. A completely stupid response but don't take it pseronally. Did you remove the # from in front of the max_connections parameter and do a COMPLETE restart? Can you post the relevent portions of your postgresql.conf file? Do you see any error messsages when starting the database up (perhaps about shared memory or such)? We also run a postfix mailserver (with maildrop, courier-imap/vauthchkpw, etc) against a postgresql database with max_connections set to 512 (FreeBSD 5.x machine). On the delivery end of things we pool the connections from the postfix server using proxymap(8) (which helped ease the connections load). Sven ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.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
Re: [GENERAL] postgres 8.x on debian
marcelo Cortez schrieb: > Hello all, > > does anyone know, whether there is a > Debian Package for postgresql 8.x for stable version? > Thanks in advance. There is no original package for postgresql v 8.x for debian sarge/stable. The only original package for this postgresql version is in unstable/sid. You can try download the source from a debian mirror near you and build a package for your debian installation. Maybe, the compiling of original source works fine under debian stable. No errors, no missing dependencies or else. With best regards -- Mario Günterberg mattheis. werbeagentur IT Engineer / Projektleiter Zillestrasse 105a. D - 10585 Berlin Tel#49-(0)30 . 34 80 633 - 0 Fax#49-(0)30 . 34 80 633 50 http://www.mattheis-berlin.de Wenn Sie glauben, dies sei ein großes Problem - warten Sie mal ab, bis wir versuchen die Lösung zu finden. (Walter Matthau) signature.asc Description: OpenPGP digital signature
[GENERAL] COMMIT in ps output
Hi all, While investigating the causes of the deadlock I described previously, we noticed that ps output would often show some postgresql processes doing COMMIT. The developer in charge of the application(*) assures me that they are not using transactions (or at least not in any of the code he checked). My question is, would postgresql ps string show COMMIT at the end of INSERT/UPDATE even if explicit transactions are not used? (*)I am posting this on behalf of another department, so I am not entirely familiar with all the crap they have writing to the database (and, evidently, neither are they ;-)). I was called in as a resident postgresql guru. But these problems are so weird that I need to turn to the experts. At one point, they had a problem with pqxx library. It would create pqxxlog_condor table and write some crap there. This caused a huge performance problem, so eventually they disabled whatever option was causing it. However, yesterday I noticed pqxxlog_condor table in the database. It was empty, so I dropped it. I checked this morning and it's back again, also empty. Could COMMITs be coming from the library? thanks in advance, Eugene Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs ---(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] Finding nearest numeric value
Richard Huxton wrote: > Poul Møller Hansen wrote: > >> Does anyone know how to find the row with the nearest numeric value, >> not necessarily an exact match ? > > > While the other answers all do their job, and in one go too, I'd be > surprised if you found anything faster than: > > SELECT myval FROM mytable WHERE myval > 1234 ORDER BY myval LIMIT 1 > UNION ALL > SELECT myval FROM mytable WHERE myval < 1234 ORDER BY myval DESC LIMIT 1 > > That gives you (up to) two values to look at, but should use any index > you have on myval. > > You can always sort the results by abs(myval) then if you don't want to > handle two values in the application layer. > Ahh, should that be >= and <= ? ;) -- Peter Fein [EMAIL PROTECTED] 773-575-0694 Basically, if you're not a utopianist, you're a schmuck. -J. Feldman ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL 8.0.3 limiting max_connections to 64 ?
On Wed, 2005-08-17 at 13:05 -0300, eu wrote: > Hello, i need help... > > i have a postgresql 8.0.3 database running on the backend of a postfix > setup ( i didn't trust Mysql for the job ) on Linux kernel 2.6.8.1, > serving email to a dozen different virtual domains ( including this one > i'm using right now )... > > however, this setup takes a whole lot of simultaneous connections to the > database > ( postfix, amavis-new, clamav + spamassassin, apache+squirrelmail, > courier-authlib and courier-imap ), all those services were configured > to use Unix Domain Sockets, instead of TCP ( netstat -anvp showed too > many short-lived connections were kept on CLOSE_WAIT for too long while > connecting via TCP ), and, despite i have made max_connections on the > postgresql.conf as high as 500, after ( around ) 64 simultaneous > connections i start having a lot of "sorry too many clients already" > errors on my postgresql logfile... > then, my users go nuts complaining about how slow the server is and/or > having to retype their passwords too many times ( of course, since > authlib can't pick a connection to authenticate against the database ). > > max_connections, shared_buffers, shmmax were tweaked, but the server > does not seems to respect the 500 max_connections... > i *know* i'm missing something obvious, but, what could be wrong ?... > i'm lost... any help would be most appreciated... please. > Can you post the relevent portions of your postgresql.conf file? Do you see any error messsages when starting the database up (perhaps about shared memory or such)? We also run a postfix mailserver (with maildrop, courier-imap/vauthchkpw, etc) against a postgresql database with max_connections set to 512 (FreeBSD 5.x machine). On the delivery end of things we pool the connections from the postfix server using proxymap(8) (which helped ease the connections load). Sven ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Finding nearest numeric value
Richard Huxton wrote: While the other answers all do their job, and in one go too, I'd be surprised if you found anything faster than: SELECT myval FROM mytable WHERE myval > 1234 ORDER BY myval LIMIT 1 Really? Aren't most things with ORDER BY O(n*log(n))? Or is the optimizer smart enough to find an index on myval and stop after the first one (assuming the index returned things sequentially. If not, it seems this could do things in O(n) time: select min(abs(value - CONSTANT)) from tablename followed by select * from tablename where abs(value - CONSTANT) = [result] though I'm sure someone could roll that up into a single statement. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] postgres 8.x on debian
am 17.08.2005, um 15:12:55 -0300 mailte marcelo Cortez folgendes: > Hello all, > > does anyone know, whether there is a > Debian Package for postgresql 8.x for stable version? > Thanks in advance. I'm useing 8.0.3 from testing in Debian/Stable without problems. Regards, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Finding nearest numeric value
To find the nearest value in number_column to some CONSTANT (where you replace constant with a number), try: select *,(number_column - CONSTANT)^2 as d from tablename order by d limit 1; Does that do it for you? Sean It does ideed, not that I understood how, but I will find out. Thank you very much. Just a word (or several) of explanation, then To compute the distance between two points on a line, you can compute the absolute value of the difference (4-2 is the same distance as 2-4, while the latter is negative) or you can square the difference (just to make it positive). You could use absolute value in the above query if you like--I don't know which is faster, but they will give the same result. As for the query structure, you can select calculations of columns as well as the columns themselves. The "as d" part just gives the calculation a nice name to use in the rest of the query and in the resulting output. Sean Thanks for the explanation, guess I was fast giving up understanding the query as it is actually quite simple :) Of course there are the performance issues as argued by others, but the table do only contain around 800 rows, so this method is adequate. Thank you all for the inputs. Poul ---(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] table clustering brings joy
Greg Stark wrote: clustering... That will only help if you're often retrieving moderately large result sets by one particular index. If you normally only retrieve one record at a time or from lots of different indexes then it probably won't really make much difference. It'll also help for columns whose values are related in some way. For example, clustering a table of addresses based on "zip code" will help lookups based on city or county or state (presumably because all the disk pages for a given city will be grouped together within the disk pages for the zip codes within the city). ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Schema design question
I originally was planning on asking about Postgresql inheritance, but after reading the docs again I do not think that it applies to my design. So, now I'm asking a rather basic schema design question. I have two related objects where one inherits column values from another. No, that's incorrect. The "child" receives default values from the "parent" when the child is created. A more concrete example: tables that represent classes taught at a school. The parent object is a general course description, and the child object is a specific instance of a course -- a "class" -- which is a course taught at a given time and location. A course can be taught multiple times, obviously. A course (and thus a class) can have multiple instructors -- a many-to-many relationship. So I have a link table for that. A class normally uses the course's default instructors, but may be different for specific classes instance. How would you layout the tables for somethings like this? I can think (out loud) of three ways to set this up: 1) Separate tables for "course" and "class" and when a class is created simply copy column data from the course to the class. Pro: Selects are simple Con: Column duplication in the two tables -- two tables look a lot alike Need to have duplicate link tables (one pointing to each table) 2) Create a third "common_values" table that both "course" and "class" tables reference. Then when creating a class from a course clone the common values row to a new row that the class can reference. Pro: No duplication of columns in multiple tables. Only need one linking table for instructors (but still need to create new links when creating the new row) Con: Need to always do joins on selects (not really a problem) 3) Create a single table with a flag to indicate if the row is a "course" or a "class". Pro: Simple selects and no column duplication between tables Con: Columns for a course might be ok as NULL, but would be required for a specific class. Again, a "course" and "class" are very similar. But, once a class is created from a course it really is its own entity. For example, if the course description changes in the future I don't want it to change on previous classes. There also needs to be a link between the two. For example, you might want to show a list of courses, and then see what classes are scheduled for a given course, so a class should reference its parent course. Thanks very much, -- Bill Moseley [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [despammed] [GENERAL] Generating random values.
am 17.08.2005, um 13:48:38 -0300 mailte Fernando Lujan folgendes: > Hi folks, > > I have a table wich contains my users... I want to insert to each user > a random password, so I need a random function. Is there such function > in Postgres? I just found the RANDOM which generates values between > 0.0 and 1.0. > > Any help or suggestion will be appreciated. :) select substring(md5(random()) from 5 for 15); Regards, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Adding contrib modules
> Jonathan Villa schrieb:
>
>> thanks, that seemed to work ok... now.. how do I use tsearch2? meaning,
>> how do I run the script? is it against the database I was to use it
>> with?
>> example
>>
>> psql -d mytestdb < tsearch2.sql
>>
>> Granted, this is a new database that has not worked with tsearch2
>> before.
>
> Yes, this is the right way. See
> $PGSRC/contrib/tsearch2/docs/tsearch-V2-intro.html chapter
> ADDING TSEARCH2 FUNCTIONALITY TO A DATABASE.
>
Thanks... at least know I'm doing to correctly... but I still get the
errors. I've done everything as it states on the tsearch-V2-intro.html
page... and then I run
psql ftstest < tsearch2.sql &> fts.out
for testing of course
the fts.out file has things like
ERROR: type "tsvector" does not exist
ERROR: type "tsquery" does not exist
ERROR: function lexize("unknown", "unknown") does not exist
I'm totally baffled... I'm running 7.4.8 by the way
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[GENERAL] postgres 8.x on debian
Hello all, does anyone know, whether there is a Debian Package for postgresql 8.x for stable version? Thanks in advance. MDC __ Correo Yahoo! Espacio para todos tus mensajes, antivirus y antispam ¡gratis! ¡Abrí tu cuenta ya! - http://correo.yahoo.com.ar ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Generating random values.
Hi Fernando; I think that PL/Perl would be the easiest language to use in this case. However, you could use PL/PGSQL and do something like: 1) Generate a random number (RANDOM()) and multiply it by a base value, and add something to it to bring it within a certain range. 2) Look up the ASCII character associated with the random number. I forget the function name, but it is listed, I think, under string functions in the docs. 3) Concatenate this onto the end of your string. The operator is ||. Doing this with a fixed-length password would be extremely easy. If you have to do it with a variable length password, then the logic will need to be a loop. THis is probably the cleanest way to do it. You could probably even do this with ANSI SQL functions with a clever case statement (I am assuming that a function is allowed to call itself). Something like: create function random_string(int, varchar) returns varchar AS ' select CASE WHEN length($2) < $1 THEN random_string($2 || chr((random() * (ascii_max - ascii_min))::int + ascii_min), $1) ELSE $2 END ' LANGUAGE SQL; Of course replace ascii_max and ascii_min with the maximum and minimum ascii values you want it to use. You can then create another function like this: CREATE FUNCTION random_string(int) returns varchar AS ' SELECT random_string($1, ); ' LANGUAGE SQL; This becomes much harder when working with Unicode, I think Best Wishes, Chris Travers Metatron Technology Consulting Fernando Lujan wrote: Hi folks, I have a table wich contains my users... I want to insert to each user a random password, so I need a random function. Is there such function in Postgres? I just found the RANDOM which generates values between 0.0 and 1.0. Any help or suggestion will be appreciated. :) Fernando Lujan ---(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 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Field order
I've searched the archives and found this question was asked in 2001 but never answered. Does the order of fields in a table make a difference? In Paradox (from whence I come), there was some belief that reliability was increased if memo (text) fields were placed at the end of the table. If field order does make a difference, does the EMS PostgreSQL manager "reorder" function physically rearrange the fields? Thanks for your time in considering this basic question
Re: [GENERAL] PostgreSQL 8.0.3 limiting max_connections to 64 ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 eu schrieb: > Hello, i need help... > > i have a postgresql 8.0.3 database running on the backend of a postfix > setup ( i didn't trust Mysql for the job ) on Linux kernel 2.6.8.1, > serving email to a dozen different virtual domains ( including this > one i'm using right now )... > > however, this setup takes a whole lot of simultaneous connections to > the database > ( postfix, amavis-new, clamav + spamassassin, apache+squirrelmail, > courier-authlib and courier-imap ), all those services were configured > to use Unix Domain Sockets, instead of TCP ( netstat -anvp showed too > many short-lived connections were kept on CLOSE_WAIT for too long > while connecting via TCP ), and, despite i have made max_connections > on the postgresql.conf as high as 500, after ( around ) 64 > simultaneous connections i start having a lot of "sorry too many > clients already" errors on my postgresql logfile... > then, my users go nuts complaining about how slow the server is and/or > having to retype their passwords too many times ( of course, since > authlib can't pick a connection to authenticate against the database ). > > max_connections, shared_buffers, shmmax were tweaked, but the server > does not seems to respect the 500 max_connections... > i *know* i'm missing something obvious, but, what could be wrong ?... > i'm lost... any help would be most appreciated... please. > > thanks. > > > > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend > > 500 parallel connections are very very much. You should verify if one application is not closing connections or if you can create an connection pool. Use select * from pg_stat_activity to see wheach connections are open from which client and if they are iddle or not. See the postgreSQL doc for more information on these queries. - -- Best Regards / Viele Grüße Sebastian Hennebrueder - http://www.laliluna.de Tutorials for JSP, JavaServer Faces, Struts, Hibernate and EJB Get support, education and consulting for these technologies - uncomplicated and cheap. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFDA3vRpqx3RdCs+9kRAqgKAKCyJcq/Zpr9YttAfXlUEhb1rfz89gCfYqVt tEukiGbcNbDmMgt0iED2NPg= =Otj6 -END PGP SIGNATURE- -- Best Regards / Viele Grüße Sebastian Hennebrueder http://www.laliluna.de Tutorials for JSP, JavaServer Faces, Struts, Hibernate and EJB Get support, education and consulting for these technologies - uncomplicated and cheap. ---(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] Checking for old transaction snapshots
On Sun, Aug 14, 2005 at 11:26:33AM -0400, Greg Stark wrote: > > I have a job that does a big batch delete/insert. I want it to vacuum (or > probably cluster) when it's finished. But I figure I should sleep for a while > before doing the vacuum to be sure there are no old transactions still > running. > > Is there a simple query I can have it do against the system tables to check > for any transactions older either than when the batch delete finished? I guess what you could do is get lock information from pg_locks. You need to know the Xid of the transactions doing the deletes; any Xid smaller than that is going to block the vacuum, and any one that started after the deletes started, too. I guess you could get the list of running Xids (from pg_locks) at the time the delete finished, and wait until all of them are gone. > I'm also interested in verifying that I don't have the problem of the > front-end application issuing a BEGIN as soon as a script ends. Ie, starting a > transaction that will lie idle until the next page hit that process handles. I think this one is harder to find out. However we should really fix this problem on the server. -- Alvaro Herrera () "No hay cielo posible sin hundir nuestras raíces en la profundidad de la tierra"(Malucha Pinto) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Technical FAQ collecting of infos from the mailing list
On Wed, Aug 17, 2005 at 06:47:33PM +0200, Sebastian Hennebrueder wrote: > Before doing this, I am having a question. Do you see in this > collecting, rewriting and structuring of mailing list information a > misuse or a copyright infringement or just would you prefer that > something like this does not happen? I think you should consider list archives to be in the public domain, i.e. do whatever you see fit. I think yours is a good idea. -- Alvaro Herrera () Management by consensus: I have decided; you concede. (Leonard Liu) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Technical FAQ collecting of infos from the mailing list
Sebastian Hennebrueder wrote: > Hello, > > I just read another nice solution for a select problem and an idea came up. > I would like to note and may be rewrite some solutions given here to > development question, tuning etc and put them on my website in a > structured way, so that looking through this is a little easier. > > Before doing this, I am having a question. Do you see in this > collecting, rewriting and structuring of mailing list information a > misuse or a copyright infringement or just would you prefer that > something like this does not happen? Sounds fine. If would be nice to reference the original authors or email messages somehow, but I don't think that is required. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Generating random values.
Fernando Lujan schrieb: >Hi folks, > >I have a table wich contains my users... I want to insert to each user >a random password, so I need a random function. Is there such function >in Postgres? I just found the RANDOM which generates values between >0.0 and 1.0. > >Any help or suggestion will be appreciated. :) > >Fernando Lujan > >---(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 > > > > Create an array of characters and numbers. Estimating the array size at 30 Calculate something like round(random * 30) and fetch a character from the array. Repeat this for each character -- Best Regards / Viele Grüße Sebastian Hennebrueder http://www.laliluna.de Tutorials for JSP, JavaServer Faces, Struts, Hibernate and EJB Get support, education and consulting for these technologies - uncomplicated and cheap. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Generating random values.
Fernando Lujan wrote: Hi folks, I have a table wich contains my users... I want to insert to each user a random password, so I need a random function. Is there such function in Postgres? I just found the RANDOM which generates values between 0.0 and 1.0. Any help or suggestion will be appreciated. :) I would do someting like: select substring(md5(random() || random()), 5, 8); Sincerely, Joshua D. Drkae Fernando Lujan ---(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 -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(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] Generating random values.
> I have a table wich contains my users... I want to insert to each user > a random password, so I need a random function. Is there such function > in Postgres? I just found the RANDOM which generates values between > 0.0 and 1.0. If you multiply that random number by a large integer and then truncate or round the result, you will get a random integer between 0 and whatever you use as a multiplier. For example, 'select round(random() * 99)' will generate a six digit random integer. Whether that's a good password generator is a completely different subject, one for which there is no 'best' answer. The more arbitrary the password, the more likely the user is to write it down or have it saved in a password file on their computer, both of which tend to defeat the purpose of having passwords in the first place. I find some rather silly password 'standards' out there. For example, one company I've done business with requires that their passwords be EXACTLY six characters long, of which two must be UPPER CASE letters, two must be lower case letters and two must be numbers. I have a short PHP program which generates (IMHO) better random passwords, using several random numbers to select two short words (2-4 characters) from a dictionary file and adding in a number. Here are a few passwords generated by it just now: caps270nods egopegs326 mast659quip semi607it rots505hot I usually generate 3 or 4 passwords then let the user pick one. I often screen the output so that I don't get passwords like this one: pissbum560 -- Mike Nolan ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] table clustering brings joy
Junaili Lie <[EMAIL PROTECTED]> writes: > Quick questions: > For big tables with frequent insert, no update, and frequent read > (using indexes), will clustering help? > what should be done on such table other than regular analyze? > comments are appreciated. If you never have any deletes or updates then you don't really need to vacuum the table regularly. (You still need to vacuum it before transaction id wraparound but that's a pretty long time.) So clustering won't help you by removing dead tuples and compacting the table. But it can still help by ordering the records in the same order as your index. The more the record order is correlated with the index the more effective the index is and the larger the result set that can use that index productively. That will only help if you're often retrieving moderately large result sets by one particular index. If you normally only retrieve one record at a time or from lots of different indexes then it probably won't really make much difference. New records won't be inserted in order though so periodically you'll want to recluster the table to maintain the order. -- greg ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] PostgreSQL 8.0.3 limiting max_connections to 64 ?
Hello, i need help... i have a postgresql 8.0.3 database running on the backend of a postfix setup ( i didn't trust Mysql for the job ) on Linux kernel 2.6.8.1, serving email to a dozen different virtual domains ( including this one i'm using right now )... however, this setup takes a whole lot of simultaneous connections to the database ( postfix, amavis-new, clamav + spamassassin, apache+squirrelmail, courier-authlib and courier-imap ), all those services were configured to use Unix Domain Sockets, instead of TCP ( netstat -anvp showed too many short-lived connections were kept on CLOSE_WAIT for too long while connecting via TCP ), and, despite i have made max_connections on the postgresql.conf as high as 500, after ( around ) 64 simultaneous connections i start having a lot of "sorry too many clients already" errors on my postgresql logfile... then, my users go nuts complaining about how slow the server is and/or having to retype their passwords too many times ( of course, since authlib can't pick a connection to authenticate against the database ). max_connections, shared_buffers, shmmax were tweaked, but the server does not seems to respect the 500 max_connections... i *know* i'm missing something obvious, but, what could be wrong ?... i'm lost... any help would be most appreciated... please. thanks. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] How to "ping" the database
The Perl DBI interface to Postgresql, module DBD::Pg, has a ping() method that is suppose to determine if a database connection is alive. It can be seen here (see: dbd_db_ping): http://search.cpan.org/src/DBDPG/DBD-Pg-1.43/dbdimp.c It pings by calling: status = _result(imp_dbh, "SELECT 'DBD::Pg ping test'"); This fails when a transaction fails -- for example when doing a serialized transaction and another session preforms an update between the serialized transaction's SELECT and UPDATE. In this situation no SELECTS are allowed until a ROLLBACK. In Perl, this failure of Ping results in a new database connection being created, even though the connection is still valid. I'm about to post a bug report on DBD::Pg, but I'm wondering if anyone here could suggest a better way to implement ping() that doesn't fail just because Postgresql is not allowing SELECTS. What I did in my code was if ping fails, call rollback and then try ping one more time. But, I'm not clear if that works in a more general case or what might happen if the connection really is broken. -- Bill Moseley [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Technical FAQ collecting of infos from the mailing list
Hello, I just read another nice solution for a select problem and an idea came up. I would like to note and may be rewrite some solutions given here to development question, tuning etc and put them on my website in a structured way, so that looking through this is a little easier. Before doing this, I am having a question. Do you see in this collecting, rewriting and structuring of mailing list information a misuse or a copyright infringement or just would you prefer that something like this does not happen? -- Best Regards / Viele Grüße Sebastian Hennebrueder http://www.laliluna.de Tutorials for JSP, JavaServer Faces, Struts, Hibernate and EJB Get support, education and consulting for these technologies ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Generating random values.
Hi folks, I have a table wich contains my users... I want to insert to each user a random password, so I need a random function. Is there such function in Postgres? I just found the RANDOM which generates values between 0.0 and 1.0. Any help or suggestion will be appreciated. :) Fernando Lujan ---(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] Adding contrib modules
Jonathan Villa schrieb: > thanks, that seemed to work ok... now.. how do I use tsearch2? meaning, > how do I run the script? is it against the database I was to use it with? > example > > psql -d mytestdb < tsearch2.sql > > Granted, this is a new database that has not worked with tsearch2 before. Yes, this is the right way. See $PGSRC/contrib/tsearch2/docs/tsearch-V2-intro.html chapter ADDING TSEARCH2 FUNCTIONALITY TO A DATABASE. With best regards -- Mario Günterberg mattheis. werbeagentur IT Engineer / Projektleiter Zillestrasse 105a. D - 10585 Berlin Tel#49-(0)30 . 34 80 633 - 0 Fax#49-(0)30 . 34 80 633 50 http://www.mattheis-berlin.de Wenn Sie glauben, dies sei ein großes Problem - warten Sie mal ab, bis wir versuchen die Lösung zu finden. (Walter Matthau) signature.asc Description: OpenPGP digital signature
Re: [GENERAL] Finding nearest numeric value
Csaba Nagy <[EMAIL PROTECTED]> writes: > The only problem is that you can't use the order by/limit syntax inside > the union queries I guess, cause the query you proposed is giving a > syntax error. Parentheses are your friend ;-) regards, tom lane ---(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] Finding nearest numeric value
On Wed, Aug 17, 2005 at 17:35:37 +0200, Csaba Nagy <[EMAIL PROTECTED]> wrote: > The only problem is that you can't use the order by/limit syntax inside > the union queries I guess, cause the query you proposed is giving a > syntax error. I also thought first to do it like this, but it won't > work. If it would, then you could wrap the thing in another query which > orders by the difference and limits to the first one ;-) You probably can just add parenthesis. I think that the second ORDER BY and LIMIT may be being applied to the UNION results which would be a problem. Putting the second subquery in parens will take care of this if that is the problem. > > On Wed, 2005-08-17 at 17:10, Richard Huxton wrote: > > Poul Møller Hansen wrote: > > > Does anyone know how to find the row with the nearest numeric value, not > > > necessarily an exact match ? > > > > While the other answers all do their job, and in one go too, I'd be > > surprised if you found anything faster than: > > > > SELECT myval FROM mytable WHERE myval > 1234 ORDER BY myval LIMIT 1 > > UNION ALL > > SELECT myval FROM mytable WHERE myval < 1234 ORDER BY myval DESC LIMIT 1 > > > > That gives you (up to) two values to look at, but should use any index > > you have on myval. > > > > You can always sort the results by abs(myval) then if you don't want to > > handle two values in the application layer. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Finding nearest numeric value
Yep, you're right. The following works and uses the index on pk_col: prepare test_01 (bigint) as select * from ( (SELECT * FROM big_table WHERE pk_col > $1 ORDER BY pk_col LIMIT 1) UNION ALL (SELECT * FROM big_table WHERE pk_col < $1 ORDER BY pk_col DESC LIMIT 1) ) as nearest order by abs(pk_col - $1) limit 1; db=> explain execute test_01(12321); QUERY PLAN Limit (cost=2.12..2.12 rows=1 width=112) -> Sort (cost=2.12..2.13 rows=2 width=112) Sort Key: abs((pk_col - $1)) -> Subquery Scan nearest (cost=0.00..2.11 rows=2 width=112) -> Append (cost=0.00..2.08 rows=2 width=59) -> Subquery Scan "*SELECT* 1" (cost=0.00..1.04 rows=1 width=59) -> Limit (cost=0.00..1.03 rows=1 width=59) -> Index Scan using idx_pk_col on big_table (cost=0.00..36639172.72 rows=35532914 width=59) Index Cond: (pk_col > $1) -> Subquery Scan "*SELECT* 2" (cost=0.00..1.04 rows=1 width=59) -> Limit (cost=0.00..1.03 rows=1 width=59) -> Index Scan Backward using idx_pk_col on big_table (cost=0.00..36639172.72 rows=35532914 width=59) Index Cond: (pk_col < $1) (13 rows) Cheers, Csaba. On Wed, 2005-08-17 at 17:57, Bruno Wolff III wrote: > On Wed, Aug 17, 2005 at 17:35:37 +0200, > Csaba Nagy <[EMAIL PROTECTED]> wrote: > > The only problem is that you can't use the order by/limit syntax inside > > the union queries I guess, cause the query you proposed is giving a > > syntax error. I also thought first to do it like this, but it won't > > work. If it would, then you could wrap the thing in another query which > > orders by the difference and limits to the first one ;-) > > You probably can just add parenthesis. I think that the second ORDER BY > and LIMIT may be being applied to the UNION results which would be a > problem. Putting the second subquery in parens will take care of this if > that is the problem. > > > > > On Wed, 2005-08-17 at 17:10, Richard Huxton wrote: > > > Poul Møller Hansen wrote: > > > > Does anyone know how to find the row with the nearest numeric value, > > > > not > > > > necessarily an exact match ? > > > > > > While the other answers all do their job, and in one go too, I'd be > > > surprised if you found anything faster than: > > > > > > SELECT myval FROM mytable WHERE myval > 1234 ORDER BY myval LIMIT 1 > > > UNION ALL > > > SELECT myval FROM mytable WHERE myval < 1234 ORDER BY myval DESC LIMIT 1 > > > > > > That gives you (up to) two values to look at, but should use any index > > > you have on myval. > > > > > > You can always sort the results by abs(myval) then if you don't want to > > > handle two values in the application layer. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Finding nearest numeric value
The only problem is that you can't use the order by/limit syntax inside the union queries I guess, cause the query you proposed is giving a syntax error. I also thought first to do it like this, but it won't work. If it would, then you could wrap the thing in another query which orders by the difference and limits to the first one ;-) Cheers, Csaba. On Wed, 2005-08-17 at 17:10, Richard Huxton wrote: > Poul Møller Hansen wrote: > > Does anyone know how to find the row with the nearest numeric value, not > > necessarily an exact match ? > > While the other answers all do their job, and in one go too, I'd be > surprised if you found anything faster than: > > SELECT myval FROM mytable WHERE myval > 1234 ORDER BY myval LIMIT 1 > UNION ALL > SELECT myval FROM mytable WHERE myval < 1234 ORDER BY myval DESC LIMIT 1 > > That gives you (up to) two values to look at, but should use any index > you have on myval. > > You can always sort the results by abs(myval) then if you don't want to > handle two values in the application layer. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Finding nearest numeric value
On 8/17/05 10:01 AM, "Poul Møller Hansen" <[EMAIL PROTECTED]> wrote: > >> To find the nearest value in number_column to some CONSTANT (where you >> replace constant with a number), try: >> >> select *,(number_column - CONSTANT)^2 as d from tablename order by d limit >> 1; >> >> Does that do it for you? >> >> Sean >> >> > It does ideed, not that I understood how, but I will find out. > Thank you very much. Just a word (or several) of explanation, then To compute the distance between two points on a line, you can compute the absolute value of the difference (4-2 is the same distance as 2-4, while the latter is negative) or you can square the difference (just to make it positive). You could use absolute value in the above query if you like--I don't know which is faster, but they will give the same result. As for the query structure, you can select calculations of columns as well as the columns themselves. The "as d" part just gives the calculation a nice name to use in the rest of the query and in the resulting output. Sean ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Finding nearest numeric value
Poul Møller Hansen wrote: Does anyone know how to find the row with the nearest numeric value, not necessarily an exact match ? While the other answers all do their job, and in one go too, I'd be surprised if you found anything faster than: SELECT myval FROM mytable WHERE myval > 1234 ORDER BY myval LIMIT 1 UNION ALL SELECT myval FROM mytable WHERE myval < 1234 ORDER BY myval DESC LIMIT 1 That gives you (up to) two values to look at, but should use any index you have on myval. You can always sort the results by abs(myval) then if you don't want to handle two values in the application layer. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Adding contrib modules
> Jonathan Villa schrieb: >> I'm having some trouble getting one of the contrib modules to load >> correctly... >> >> it's for tsearch2.sql >> >> I've tried >> >> ./configure --prefix=/usr/local/pgsql >> gmake all >> gmake install >> >> and I've also tried, from the contrib/tsearch2/ dir, >> >> gmake >> >> but that fails when looking for flex, which by the way, is installed. >> >> When attempting the former method, and trying to run >> >> psql -d mydb -f tsearch2.sql >> >> I get all kinds of errors... >> >> specifically, >> >> type "tsvector" does not exist >> >> >> I've googled for a solution/explaination but I'm still in the dark. > > Try from source root gmake -C contrib/tsearch2 and > gmake -C contrib/tsearch2 install. > > What version of flex is installed? > Try flex --version. > On my machine works flex 2.5.4 fine. > > thanks, that seemed to work ok... now.. how do I use tsearch2? meaning, how do I run the script? is it against the database I was to use it with? example psql -d mytestdb < tsearch2.sql Granted, this is a new database that has not worked with tsearch2 before. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Postgresql server "crash" after some day
"Stefano B." <[EMAIL PROTECTED]> writes: > I can make a connection from local host but I can't from remote host. If = > I try to make a connection from remote host the server returns me the = > classic remote error: > could not connect to server ... is the server running on host ... = > and accepting TCP/IP connections on port 5432? You've suppressed the part of that message that's actually useful --- what was the kernel error message exactly? > This problem happens after some day that I have start the server. I'm betting it's a firewall kind of problem. What is between your remote host and the database server? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How to implement table caching
There is also http://people.freebsd.org/~seanc/pgmemcache/
On Mon, Aug 15, 2005 at 04:54:31PM -0500, Thomas F. O'Connell wrote:
> Andrus,
>
> You might consider something like materialized views:
>
> http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html
>
> Whether table caching is a good idea depends completely on the
> demands of your application.
>
> --
> Thomas F. O'Connell
> Co-Founder, Information Architect
> Sitening, LLC
>
> Strategic Open Source: Open Your i?
>
> http://www.sitening.com/
> 110 30th Avenue North, Suite 6
> Nashville, TN 37203-6320
> 615-469-5150
> 615-469-5151 (fax)
>
> On Aug 14, 2005, at 1:12 PM, Andrus Moor wrote:
>
> >To increase performance, I'm thinking about storing copies of less
> >frequently changed tables in a client computer.
> >At startup client application compares last change times and
> >downloads newer
> >tables from server.
> >
> >CREATE TABLE lastchange (
> > tablename CHAR(8) PRIMARY KEY,
> > lastchange timestamp without time zone );
> >
> >INSERT INTO lastupdated (tablename) values ('mytable1');
> >
> >INSERT INTO lastupdated (tablename) values ('mytablen');
> >
> >CREATE OR REPLACE FUNCTION setlastchange() RETURNS "trigger"
> >AS $$BEGIN
> >UPDATE lastchange SET lastchange='now' WHERE tablename=TG_RELNAME;
> >RETURN NULL;
> >END$$ LANGUAGE plpgsql STRICT;
> >
> >CREATE TRIGGER mytable1_trig BEFORE INSERT OR UPDATE OR DELETE ON
> >mytable1
> > EXECUTE PROCEDURE setlastchange();
> >
> >CREATE TRIGGER mytablen_trig BEFORE INSERT OR UPDATE OR DELETE ON
> >mytablen
> > EXECUTE PROCEDURE setlastchange();
> >
> >Is table caching good idea?
> >Is this best way to implement table caching ?
> >
> >Andrus.
>
> ---(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
>
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Softwarehttp://pervasive.com512-569-9461
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Finding nearest numeric value
Sean Davis wrote: > On 8/17/05 8:50 AM, "Poul Møller Hansen" <[EMAIL PROTECTED]> wrote: > > >>Does anyone know how to find the row with the nearest numeric value, not >>necessarily an exact match ? > > > To find the nearest value in number_column to some CONSTANT (where you > replace constant with a number), try: > > select *,(number_column - CONSTANT)^2 as d from tablename order by d limit > 1; > Save yourself some cycles - use abs() instead of exponentiation. -- Peter Fein [EMAIL PROTECTED] 773-575-0694 Basically, if you're not a utopianist, you're a schmuck. -J. Feldman ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Finding nearest numeric value
[snip] > To find the nearest value in number_column to some CONSTANT (where you > replace constant with a number), try: > > select *,(number_column - CONSTANT)^2 as d from tablename order by d limit > 1; > This will scan the whole table and sort the results... and then pick just one of it. Watch this: db=> prepare test_01(bigint) as select *, (pk_col - $1) ^ 2 as d from big_table order by d limit 1; PREPARE eb=> explain execute test_01(27163619); QUERY PLAN - Limit (cost=31239164.71..31239164.72 rows=1 width=59) -> Sort (cost=31239164.71..31505657.00 rows=106596914 width=59) Sort Key: (((pk_col - $1))::double precision ^ 2::double precision) -> Seq Scan on big_table (cost=0.00..3149688.00 rows=106596914 width=59) (4 rows) The names were changed, this is a production DB, but the idea is: big_table has around 100 million rows, and pk_col is the primary key on it. Running the above query would take forever. If you don't have an index on the numeric column, or if the table is small, this might be your best choice... but if your table is big, and you have an index on the numeric column, you should use something along: select * number_col from big_table where number_col < CONSTANT order by number_col desc limit 1 select * number_col from big_table where number_col > CONSTANT order by number_col limit 1 You execute the 2 queries, which are very fast even for big tables if you have an index on number_col, and then choose the row with the smallest difference (you do this in your client program). HTH, Csaba. > Does that do it for you? > > Sean > > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Finding nearest numeric value
To find the nearest value in number_column to some CONSTANT (where you replace constant with a number), try: select *,(number_column - CONSTANT)^2 as d from tablename order by d limit 1; Does that do it for you? Sean It does ideed, not that I understood how, but I will find out. Thank you very much. Poul ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] cobol storedprocedures
> I've been scanning the postgres website and yours to find out any > information on cobol stored procedures. Is there any plans on > incorporating this in future releases? Not really. The 'preferred' sorts of languages to include for stored procedures tend to be interpreted languages. There is something of a paucity of free cross-platform COBOL implementations, and what I've heard of have been compiled, which makes them somewhat less ideal... The best chance might be to locate some COBOL-to-Java translator, and look into linking the results in to one of the Java stored procedure systems... -- (reverse (concatenate 'string "moc.liamg" "@" "enworbbc")) http://cbbrowne.com/info/cobol.html If we were meant to fly, we wouldn't keep losing our luggage. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Finding nearest numeric value
On 8/17/05 8:50 AM, "Poul Møller Hansen" <[EMAIL PROTECTED]> wrote: > Does anyone know how to find the row with the nearest numeric value, not > necessarily an exact match ? To find the nearest value in number_column to some CONSTANT (where you replace constant with a number), try: select *,(number_column - CONSTANT)^2 as d from tablename order by d limit 1; Does that do it for you? Sean ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Postgresql server "crash" after some day
Stefano B. schrieb: Hi all, I have some strange problems with postgres. After some days it works in local but it doesn't listen on port 5432 for remote request. I can make a connection from local host but I can't from remote host. If I try to make a connection from remote host the server returns me the classic remote error: could not connect to server ... is the server running on host ... and accepting TCP/IP connections on port 5432? This problem happens after some day that I have start the server. There is no particular error in the log file (the server seems to work) PostgreSql version: 8.0.3 on FreeBSD 5.3 after some day? If you did not change the config files and if there was not another process using that port during the start, chances are you configured a firewall which just refuses connections to port 5432? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Postgresql server "crash" after some day
Is it possible that you're application is not closing connections, and the server has a limit on connection count, and that is reached in a few days ? Actually I'm not sure if the "max_connections" parameter is applicable to local connections too, cause in that case you shouldn't be able to connect locally either... Just a thought. Cheers, Csaba. On Wed, 2005-08-17 at 14:55, Stefano B. wrote: > Hi all, > > I have some strange problems with postgres. > After some days it works in local but it doesn't listen on port 5432 > for remote request. > > I can make a connection from local host but I can't from remote host. > If I try to make a connection from remote host the server returns me > the classic remote error: > > could not connect to server ... is the server running on host ... > and accepting TCP/IP connections on port 5432? > > This problem happens after some day that I have start the server. > There is no particular error in the log file (the server seems to > work) > > PostgreSql version: 8.0.3 > on FreeBSD 5.3 > > Thanks in advance. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] BUG #1830: Non-super-user must be able to copy from a file
On Wed, Aug 17, 2005 at 09:22:16 +0100, Bernard <[EMAIL PROTECTED]> wrote: > > The following bug has been logged online: This isn't a bug and you really should have asked this question on another list. I am moving the discussion over to the general list. > > Bug reference: 1830 > Logged by: Bernard > Email address: [EMAIL PROTECTED] > PostgreSQL version: 8.0.3 > Operating system: Linux RedHat 9 > Description:Non-super-user must be able to copy from a file > Details: > > On the attempt to bulk load a table from a file that is owned by the > non-superuser current database user, the following error message is > printed: > > "must be superuser to COPY to or from a file" > > What is the reason for this limitation? This is described in the documentation for the copy command. > > It can't justifiably be for security reasons because if a web application > such as tomcat requires to bulk load tables automatically on a regular basis > then one would be forced to let the web application connect as superuser, > which is very bad for security. No, because you can have the app read the file and then pass the data to the copy command. To do this you use STDIN as the file name. > > In MySQL bulk loading works for all users. You can use the \copy command in psql to load data from files. > > We need a Postgresql solution. > > We have a web application where both MySQL and Postresql are supported. With > Postgresql, the application would have to connect as user postgres. We have > to explain this security risk to our clients very clearly. > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Set autocommit to off
Andreas Kretschmer schrieb: Aliomar Mariano Rego <[EMAIL PROTECTED]> schrieb: Does somebody knows why the Postgresql 7.4.8 or later doesn't supports the option "SET AUTOCOMMIT TO OFF"? \set AUTOCOMMIT off works fine in 8.0.3 yes, buts psql, not the backend :-) ---(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] Set autocommit to off
On Wed, Aug 17, 2005 at 08:24:00AM +, Aliomar Mariano Rego wrote: > Does somebody knows why the Postgresql 7.4.8 or later doesn't supports > the option "SET AUTOCOMMIT TO OFF"? Because server-side autocommit was a bad idea. See the 7.4 Release Notes. http://www.postgresql.org/docs/7.4/static/release-7-4.html "The server-side autocommit setting was removed and reimplemented in client applications and languages. Server-side autocommit was causing too many problems with languages and applications that wanted to control their own autocommit behavior, so autocommit was removed from the server and added to individual client APIs as appropriate." -- Michael Fuhr ---(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] Postgresql server "crash" after some day
Hi all, I have some strange problems with postgres. After some days it works in local but it doesn't listen on port 5432 for remote request. I can make a connection from local host but I can't from remote host. If I try to make a connection from remote host the server returns me the classic remote error: could not connect to server ... is the server running on host ... and accepting TCP/IP connections on port 5432? This problem happens after some day that I have start the server. There is no particular error in the log file (the server seems to work) PostgreSql version: 8.0.3 on FreeBSD 5.3 Thanks in advance.
[GENERAL] Finding nearest numeric value
Does anyone know how to find the row with the nearest numeric value, not necessarily an exact match ? Thanks, Poul ---(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] Set autocommit to off
Aliomar Mariano Rego <[EMAIL PROTECTED]> schrieb: > Does somebody knows why the Postgresql 7.4.8 or later doesn't supports > the option "SET AUTOCOMMIT TO OFF"? \set AUTOCOMMIT off works fine in 8.0.3 Regards, Andreas -- Diese Message wurde erstellt mit freundlicher Unterstützung eines freilau- fenden Pinguins aus artgerechter Freilandhaltung. Er ist garantiert frei von Micro$oft'schen Viren. (#97922 http://counter.li.org) GPG 7F4584DA Was, Sie wissen nicht, wo Kaufbach ist? Hier: N 51.05082°, E 13.56889° ;-) ---(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] cobol storedprocedures
On Wed, Aug 17, 2005 at 01:05:07PM +0200, Dawid Kuroczko wrote: > On 8/15/05, MICHAEL BATTANI <[EMAIL PROTECTED]> wrote: > > I've been scanning the postgres website and yours to find out any > > information on cobol stored procedures. Is there any plans on incorporating > > this > > in future releases? > > I don't think anyone is working on such a thing right now. > > The procedural languages development usually follows this route: It's even more complicated than this I think in this case. Pretty much all of the procedural languages currently supported are interpreted, it's easier that way. Compiled (C) functions need to be compiled and loaded specially, which I imagine is not what the original poster wanted. Looking on the web for a free (open source) COBOL interpreters[1][2] has not been fruitful which just places another roadblock. I know nothing about COBOL so I have no idea how hard it would be to write one. Perhaps translating to another interpreted language would be easier. At least COBOL to C converters seem to be in abundence. [1] http://www.thefreecountry.com/compilers/cobol.shtml [2] http://www.ecuadors.net/compilers.htm Hope this helps, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpS8kQ4mJak6.pgp Description: PGP signature
Re: [GENERAL] Set autocommit to off
On 8/17/05 4:24 AM, "Aliomar Mariano Rego" <[EMAIL PROTECTED]> wrote: > Does somebody knows why the Postgresql 7.4.8 or later doesn't supports > the option "SET AUTOCOMMIT TO OFF"? See this message: http://archives.postgresql.org/pgsql-general/2005-07/msg00064.php In short, server-side autocommit is not possible after 7.3, I think. Instead, use a transaction block (BEGIN...COMMIT) or set it on the client side like this in psql: http://www.postgresql.org/docs/8.0/interactive/app-psql.html and search for autocommit. Hope that helps Sean ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Set autocommit to off
Does somebody knows why the Postgresql 7.4.8 or later doesn't supports the option "SET AUTOCOMMIT TO OFF"? Thanks. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] cobol storedprocedures
On 8/15/05, MICHAEL BATTANI <[EMAIL PROTECTED]> wrote: > I've been scanning the postgres website and yours to find out any > information on cobol stored procedures. Is there any plans on incorporating > this > in future releases? I don't think anyone is working on such a thing right now. The procedural languages development usually follows this route: 1. Someone skilled in C needs some procedural language, be it Perl, Python, Ruby, etc. 2. This person "hacks" a "glue" code in C for such a language -- this step is actually relatively easy -- you just have to create code similar to already existing procedural languages. 3. This person releases the code, probably as a pg_foundry code, announces it and so on. 4. If language receives significant response it may be moved into core system. If it does not or for some reason (is not mature enough, user base is too small, nobody feels a need to drive the process), it is still available as pg_foundry or similar project -- you have to download it seperately -- it is the case with PL/Ruby, PL/Java, PL/J. The problem is finding that 'someone'. The law of big numbers states that given large enough population of developers, you will find this 'someone' in this group. ;) Personally I do not know Cobol, do not know any active Cobol coders and do not know any Cobol implementation internals (how difficult is it to plug it in as an embedded language). One question you have to answer yourself is what do you need Cobol for? There is a high chance that PL/perl, PL/python, PL/ruby or PL/R will do the thing you need, but have advantage of being "already there". Regards, Dawid ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] winxp with 8.0.3 postgresql
Hi, I got the similar problem - as i understood, the latest odbc driver do not understand some text fields/ i have solved the problem after installing older drivers, such as :psqlodbc-07_02_0004.zip DBC> I use in my application the BDE 5.01 with psqlodbc DBC> After instalation the postgresql 8.0.3 in my winxp with sp2 or sp1 in two cases the simple SQL comand with BDE result Error: Bad field type. DBC> I go select * from usuarios and i go normally DBC> I go select nome, codigo from usuarios i go Error: Bad field type. DBC> What is this?? DBC> Thanks DBC> Dario -- Ñ óâàæåíèåì, Èãîðü mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] winxp with 8.0.3 postgresql
Hi, I use in my application the BDE 5.01 with psqlodbc After instalation the postgresql 8.0.3 in my winxp with sp2 or sp1 in two cases the simple SQL comand with BDE result Error: Bad field type. I go select * from usuarios and i go normally I go select nome, codigo from usuarios i go Error: Bad field type. What is this?? Thanks Dario
[GENERAL] pgsql-bugs
From: "Lee Hyun soon" <[EMAIL PROTECTED]> To: pgsql-bugs@postgresql.org Date: Wed, 17 Aug 2005 05:36:23 +0100 (BST) Subject: BUG #1826: pgsql odbc & ADO.NET The following bug has been logged online: Bug reference: 1826 Logged by: Lee Hyun soon Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0.2 Operating system: Windows XP Professional SP1 Description:pgsql odbc & ADO.NET Details: I'm a Corean Coder. During C# Coding, I Found it. - http://www.windows.or.kr/zboard/bbs/view.php?id=app1data&page=1&sn1=&divpage =1&sn=off&ss=on&sc=on&select_arrange=headnum&desc=asc&no=6 --- Download #1 : RichEditBoxTest.zip (18.6 KB) Download #2 : input_data.txt (3.2 KB) RichEditBoxTest.zip is Test Program(C#.net) input_data.txt is input Data i use latest odbc provider, and it's database scheme is == CREATE TABLE nmsdata ( orgin_code char(17) NOT NULL DEFAULT to_char(now(), 'mmddHH24MISSMS'::text), data text, datetime timestamp DEFAULT now(), bigo1 varchar(50), "year" char(4), data_gubun numeric DEFAULT 0 ) WITHOUT OIDS; ALTER TABLE nmsdata OWNER TO postgres; GRANT ALL ON TABLE nmsdata TO postgres; GRANT ALL ON TABLE nmsdata TO public; == and, odbc dsn is "remote" the problem is short string is "insert" DML processing completely. and long string is also. but, after shot string "select" DML, data's tail is broken. u see my source(if u know C# Language and have .Net Comfiler), u catch this problem. - i cannot use english T^T well. i hope that u catch my problem. and this problem will be solved. - Corean(Korean) Lee. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] libpy and ENABLE_THREAD_SAFETY=1
Magnus Hagander wrote: Is the standard libpq.dll distributed by PostgreSQL8.0 for windows thread safe by default ? No. It is safe as long as you use each PGconn on a separate thread but you cannot share the same PGconn between threads. //Magnus But what if I compile the DLL using ENABLE_THREAD_SAFETY ? Can I then use one connection for multiple threads ? Pit ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] libpy and ENABLE_THREAD_SAFETY=1
Magnus Hagander wrote: Is the standard libpq.dll distributed by PostgreSQL8.0 for windows thread safe by default ? No. It is safe as long as you use each PGconn on a separate thread but you cannot share the same PGconn between threads. //Magnus But what if I compile the DLL using ENABLE_THREAD_SAFETY ? Can I then use one connection for multiple threads ? In theory, but I beleive there are build issues in the currently released version when it comes to building on win32 with ENABLE_THREAD_SAFETY. //Magnus Thank you for this information. Your wrote: "In theory" Do you know anybody who has tested this ? I mean a person of the PostgreSQL core team. Pit ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] cobol storedprocedures
I've been scanning the postgres website and yours to find out any information on cobol stored procedures. Is there any plans on incorporating this in future releases? Michael Battani Companion Technologies 8901 Farrow Road Columbia, SC 29203 803.264.3569 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
