Re: [GENERAL] Using Sockets in postgres
> I've made a client and a server program in C using socket library.
>
> My goal is to compile the client as a shared object, load it
> dynamically in postgres (in a function), execute that
> postgres function in which this shared library (client) is
> dynamically loaded and connect to the server (hosted on some
> other machine outside the domain of postgres database server).
> In otherwords, connecting to another server through postgres
> database using the client code dynamically loaded in postgres.
Have you considered using dblink from the contrib modules?
> Now, here starts the problem. In my client code when i use
> functions like gethostbyname as follows,
>
> server = gethostbyname("www.yahoo.com");
> serv_addr.sin_addr.s_addr = inet_addr("server");
>
> It closes my postgres server and gives the follwowing error message:-
>
> server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
>
> After commenting my code at various lines, I figured out that
> gethostbyname() function is the cause of this error message.
Did you get a core dump? If not, check your ulimits (assuming that
you are on some flavour of UNIX).
Use the degugger to examine the core dump, that might give some insight.
Yours,
Laurenz Albe
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
[GENERAL] Using Sockets in postgres
Hi,I've made a client and a server program in C using socket library.The client is in the postgres database server and Server is on some other machine.My goal is to compile the client as a shared object, load it dynamically in postgres (in a function), execute that postgres function in which this shared library (client) is dynamically loaded and connect to the server (hosted on some other machine outside the domain of postgres database server).
In otherwords, connecting to another server through postgres database using the client code dynamically loaded in postgres.Now, here starts the problem. In my client code when i use functions like gethostbyname as follows,
server = gethostbyname("www.yahoo.com");serv_addr.sin_addr.s_addr = inet_addr("server");It closes my postgres server and gives the follwowing error message:-
server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.After commenting my code at various lines, I figured out that gethostbyname() function is the cause of this error message.
Can anyone please throw light on this problem.Thanks,~Jas
RES: RES: RES: RES: [GENERAL] Dates rejected
Hi Tom, I think I´ve got it... If you change something in the timezone file that is specified in postgresq.conf, PG will know this changes automatically? Am I right? Carlos > -Mensagem original- > De: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] nome de Tom Lane > Enviada em: terça-feira, 17 de outubro de 2006 00:02 > Para: [EMAIL PROTECTED] > Cc: Pgsql-General@Postgresql.Org > Assunto: Re: RES: RES: RES: [GENERAL] Dates rejected > > > "Carlos H. Reimer" <[EMAIL PROTECTED]> writes: > > To discover if it works this way I´ve changed the > /etc/localtime to relect > > the following timezone: > > Um ... what PG version are you working with? 8.0 and up don't pay > attention to /etc/localtime, because they have their own timezone info. > > regards, tom lane > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org/ > > ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Aother tablespace permission issue (was Re: [GENERAL] Permission problem ...)
On 10/16/06 20:17, Douglas McNaught wrote: > "Andras Simon" <[EMAIL PROTECTED]> writes: > >> I'm trying to create a new tablespace in a directory that postgres >> owns, but PG says it cannot set permissions on this directory. > > ... > >> This is on Fedora Core 5 (x86), psql 8.1.4. > > SELinux, most likely. In my case, it is *not* SELinux, since I'm not running it... Debian Sid PostgreSQL 8.1.5 (distro version 8.1.5-1) As you can see from the attached file, user "me" is a Superuser and is a member of group postgres, and *is* able to create files in that directory. File /var/log/postgresql/postgresql-8.1-main.log gives the same error that psql does, and there's nothing in syslog. Any help appreciated. -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. ~$ dir /data/02/share | grep datab drwxrwxr-x 2 me postgres 4096 2006-10-16 21:38 database/ ~$ touch /data/02/share/database/testing.testing ~$ dir /data/02/share/database total 8 drwxrwxr-x 2 me postgres 4096 2006-10-16 21:53 ./ drwxrwxr-x 16 me people 4096 2006-10-16 21:38 ../ -rw-r--r-- 1 me me 0 2006-10-16 21:53 testing.testing $ psql -U me dupe_filenames Welcome to psql 8.1.5, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit dupe_filenames=# \du List of roles Role name | Superuser | Create role | Create DB | Connections | Member of ---+---+-+---+-+--- me| yes | yes | yes | no limit| postgres | yes | yes | yes | no limit| (2 rows) dupe_filenames=# create tablespace thisisatest dupe_filenames-# owner me dupe_filenames-# location '/data/02/share/database'; ERROR: could not set permissions on directory "/data/02/share/database": Operation not permitted signature.asc Description: OpenPGP digital signature
RES: RES: RES: RES: [GENERAL] Dates rejected
Yes, it is the 8.0.8 version. You mean that changes to /etc/localtime should not reflect in the to_timestamp behavior? Strange, why does to_timestamp behavior changed here when the /etc/localtime was overlaped? Well, maybe the PG refresh I´ve done after changing the /etc/localtime did it (pg_ctl restart). Anyway, if it will not look at the /etc/localtime, how PG will discover the DST changes? Thank you in advance! Carlos > -Mensagem original- > De: Tom Lane [mailto:[EMAIL PROTECTED] > Enviada em: terça-feira, 17 de outubro de 2006 00:02 > Para: [EMAIL PROTECTED] > Cc: Pgsql-General@Postgresql.Org > Assunto: Re: RES: RES: RES: [GENERAL] Dates rejected > > > "Carlos H. Reimer" <[EMAIL PROTECTED]> writes: > > To discover if it works this way I´ve changed the > /etc/localtime to relect > > the following timezone: > > Um ... what PG version are you working with? 8.0 and up don't pay > attention to /etc/localtime, because they have their own timezone info. > > regards, tom lane > > ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] [ANN] VTD-XML 1.7 released
XimpleWare released Version 1.7 of VTD-XML, the next generation XML parser that goes beyond DOM and SAX, under GPL. VTD-XML is getting faster, leaner, more stable and complete with this release. New features included in this releases are: * Additional XPath functions support * Union Expression * A rewritten VTDNav implementation * Various bug fixes in VTDGen and XPath evaluation Please visit http://vtd-xml.sf.net for the latest release. For further reading, please refer to the following articles: a.. Simplify XML Processing with VTD-XML a.. Cut, Paste, Assemble and Split XML files with VTD-XML a.. XML on a Chip a.. Process Large XML Files with VTD-XML a.. The performance Woe of Binary XML a.. VTD-XML: The Next Generation XML Parser ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: RES: RES: RES: [GENERAL] Dates rejected
"Carlos H. Reimer" <[EMAIL PROTECTED]> writes: > To discover if it works this way I´ve changed the /etc/localtime to relect > the following timezone: Um ... what PG version are you working with? 8.0 and up don't pay attention to /etc/localtime, because they have their own timezone info. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
RES: RES: RES: [GENERAL] Dates rejected
Hi Tom,
Thank you very much for your explanation!
Let me know if I´ve understood correctly:
If I move the first day DST from Oct 15th to Nov 05th, then the to_timestamp
should show the offset on day Nov 05th and not anymore on Oct 15th, right?
To discover if it works this way I´ve changed the /etc/localtime to relect
the following timezone:
Rule Brazil 2006 only - Nov 05 00:00 1 S
Rule Brazil 2007 only - Feb 25 00:00 0 -
Zone Brazil/hv2006 -3:00 Brazil BR%sT
And tried:
template1=# select pg_catalog.to_timestamp('15/10/2006','dd/mm/');
to_timestamp
2006-10-15 00:00:00-03
(1 row)
Ok, the result now is reflecting the DST changing of the timezone.
Another tried:
template1=# select pg_catalog.to_timestamp('05/11/2006','dd/mm/');
to_timestamp
2006-11-05 00:00:00-03
(1 row)
Should it not show 2006-11-05 01:00:00-02 as happened before with date
15/10/2006 (dd/mm/)?
Am I missing something?
Thanks in advance!
Carlos
> -Mensagem original-
> De: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] nome de Tom Lane
> Enviada em: segunda-feira, 16 de outubro de 2006 21:38
> Para: [EMAIL PROTECTED]
> Cc: Andreas Kretschmer; Pgsql-General@Postgresql.Org
> Assunto: Re: RES: RES: [GENERAL] Dates rejected
>
>
> "Carlos H. Reimer" <[EMAIL PROTECTED]> writes:
> > The problem is related with the to_timestamp function that
> returns +1 hour
> > offset only for the date 15/10/2006. The 15th october is the
> first day of
> > our day light change.
>
> The reason is that it's generating '2006-10-15 00:00:00-03' to start
> with, but there *is* no such time in your time zone: that was the
> instant that the clocks stepped forward, and so it's equally legitimate
> to display that time as '2006-10-15 01:00:00-02', which is what in fact
> our code happens to do. Then when you coerce the timestamp with time
> zone down to plain timestamp, the offset info that might have cued you
> what's going on goes away...
>
> I believe the lack of a definite midnight hour is one reason why most
> countries prefer to change their clocks at some other time of night.
>
> regards, tom lane
>
> ---(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 6: explain analyze is your friend
Re: [GENERAL] Permission problem with create tablespace
On 10/17/06, Tom Lane <[EMAIL PROTECTED]> wrote: To use a nondefault tablespace, you'd want to tweak the policy to allow postgres to write that directory tree too. I'm afraid I know too little about selinux to explain exactly what to do though ... need to learn that someday ... Me too... A quick and dirty solution is to put selinux into non-enforcing mode, but if this machine is exposed to the internet at all, that's probably not a good answer. Yes, I'd like to avoid that if at all possible. Andras ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Permission problem with create tablespace
On 10/17/06, Douglas McNaught <[EMAIL PROTECTED]> wrote:
"Andras Simon" <[EMAIL PROTECTED]> writes:
> I'm trying to create a new tablespace in a directory that postgres
> owns, but PG says it cannot set permissions on this directory.
...
> This is on Fedora Core 5 (x86), psql 8.1.4.
SELinux, most likely.
That's it!
Oct 17 02:58:57 localhost kernel: audit(1161046737.127:24): avc: denied { setattr } for
pid=21374 comm="postmaster" name="mspace" dev=hdb6 ino=2424835
scontext=system_u:system_r:postgresql_t:s0 tcontext=user_u:object_r:usr_t:s0 tclass=dir
Thanks,
Andras
---(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] Permission problem with create tablespace
"Andras Simon" <[EMAIL PROTECTED]> writes: > I'm trying to create a new tablespace in a directory that postgres > owns, but PG says it cannot set permissions on this directory. > ... > This is on Fedora Core 5 (x86), psql 8.1.4. Do you have selinux enabled? The default selinux policy disallows the postgres daemon from writing anywhere outside the standard /var/lib/pgsql/data directory tree. Check for "avc denied" messages in the kernel log to verify this. To use a nondefault tablespace, you'd want to tweak the policy to allow postgres to write that directory tree too. I'm afraid I know too little about selinux to explain exactly what to do though ... need to learn that someday ... A quick and dirty solution is to put selinux into non-enforcing mode, but if this machine is exposed to the internet at all, that's probably not a good answer. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Permission problem with create tablespace
> I'm trying to create a new tablespace in a directory that postgres > owns, but PG says it cannot set permissions on this directory. > > [EMAIL PROTECTED] ~]# su - postgres > -bash-3.1$ mkdir /opt/home/pgdata/mspace/ > -bash-3.1$ psql > Welcome to psql 8.1.4, the PostgreSQL interactive terminal. > > Type: \copyright for distribution terms >\h for help with SQL commands >\? for help with psql commands >\g or terminate with semicolon to execute query >\q to quit > > postgres=# CREATE TABLESPACE mspace LOCATION '/opt/home/pgdata/mspace'; > ERROR: could not set permissions on directory > "/opt/home/pgdata/mspace": Permission denied > postgres=# \q > -bash-3.1$ chmod 700 /opt/home/pgdata/mspace/ > -bash-3.1$ whoami > postgres > -bash-3.1$ > > This is on Fedora Core 5 (x86), psql 8.1.4. > > Andras > > ---(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 > Check for SELinux permissions Leonel ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Permission problem with create tablespace
"Andras Simon" <[EMAIL PROTECTED]> writes: > I'm trying to create a new tablespace in a directory that postgres > owns, but PG says it cannot set permissions on this directory. ... > This is on Fedora Core 5 (x86), psql 8.1.4. SELinux, most likely. -Doug ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [HACKERS] [GENERAL] Anyone using "POSIX" time zone offset capability?
I wrote: > ... I'm not entirely convinced that it really is a POSIX-sanctioned > notation, either --- the POSIX syntax the zic code knows about is > different. Actually, I take that back: it is a subset of the same notation, but the datetime.c code is misinterpreting the spec! The POSIX timezone notation as understood by the zic code includes the possibility of zoneabbrev[+-]hh[:mm[:ss]] but the meaning is that hh:mm:ss *is* the offset from GMT, and zoneabbrev is being defined as the abbreviation for that offset. What the datetime.c code is doing is trying to find the zoneabbrev in a built-in timezone table, and then adding the two together. This is simply wacko. Given where the code stands now, I think the best solution is to rip out DecodePosixTimezone and instead pass the syntax off to the zic code (which can handle it via tzparse()). Since the datetime input parser is ultimately only interested in the GMT offset value, this would mean that the zoneabbrev part would become a noise word. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Permission problem with create tablespace
I'm trying to create a new tablespace in a directory that postgres owns, but PG says it cannot set permissions on this directory. [EMAIL PROTECTED] ~]# su - postgres -bash-3.1$ mkdir /opt/home/pgdata/mspace/ -bash-3.1$ psql Welcome to psql 8.1.4, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit postgres=# CREATE TABLESPACE mspace LOCATION '/opt/home/pgdata/mspace'; ERROR: could not set permissions on directory "/opt/home/pgdata/mspace": Permission denied postgres=# \q -bash-3.1$ chmod 700 /opt/home/pgdata/mspace/ -bash-3.1$ whoami postgres -bash-3.1$ This is on Fedora Core 5 (x86), psql 8.1.4. Andras ---(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] pg_locks: who is locking ?
Alexandre Arruda <[EMAIL PROTECTED]> writes: > But pg_stat_activity joined with pg_locks only give me informations > about the lock itself. > Realy, I want a (possible) simple information: Who is locking me ? You need a self-join to pg_locks to find the matching lock that is held (not awaited) by some process, then join that to pg_stat_activity to find out who that is. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] datatype preceded by underscore creates array
"George Pavlov" <[EMAIL PROTECTED]> writes: > yes, but int8 is a clearly documented while preceding certain "magic" > datatype names with underscores is not. i really don't have much of a > problem with this, but little things like this contribute to people > coming from other DBMSs developing opinions that "this open-source stuff > does weird, undocumented things" (not that commercial DBMSs don't do > weird stuff :). Undocumented? Read the CREATE TYPE manual page. I'll agree it's pretty ugly, but it's not worth changing ... especially since a change would break some legacy application code. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: RES: RES: [GENERAL] Dates rejected
"Carlos H. Reimer" <[EMAIL PROTECTED]> writes: > The problem is related with the to_timestamp function that returns +1 hour > offset only for the date 15/10/2006. The 15th october is the first day of > our day light change. The reason is that it's generating '2006-10-15 00:00:00-03' to start with, but there *is* no such time in your time zone: that was the instant that the clocks stepped forward, and so it's equally legitimate to display that time as '2006-10-15 01:00:00-02', which is what in fact our code happens to do. Then when you coerce the timestamp with time zone down to plain timestamp, the offset info that might have cued you what's going on goes away... I believe the lack of a definite midnight hour is one reason why most countries prefer to change their clocks at some other time of night. regards, tom lane ---(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] Anyone using "POSIX" time zone offset capability?
"Brandon Aiken" <[EMAIL PROTECTED]> writes: > What about time zones like Tehran (GMT+3:30), Kabul (GMT+4:30), Katmandu > (GMT+5:45) and other non-cardinal-hour GMT offsets? Is this handled in > some *documented* way already? Sure. This has worked since PG 7.2 or so: regression=# select '12:34:00 IRT'::timetz; timetz 12:34:00+03:30 (1 row) Also you can just do regression=# select '12:34:00 +03:30'::timetz; timetz 12:34:00+03:30 (1 row) regression=# The weird thing about this allegedly-POSIX notation is the combination of a symbolic name and a further offset from it. Back when we didn't have customizable timezone abbreviations, maybe there would be some point in making that work, but I don't see the point now. I'm not entirely convinced that it really is a POSIX-sanctioned notation, either --- the POSIX syntax the zic code knows about is different. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] pg_locks: who is locking ?
Alvaro Herrera escreveu: Alexandre Arruda wrote: Hi, My Database have a lot of locks not granted every moments in a day. Can I create a view that returns someting like this ? UserGranted Table Who_is_locking_me PID --- - - --- joe f foo frank 1212 jefff foo frank 1313 ann f foo frank 1414 frank t foo (...) (Or the locked transactions, if the table cold't be retrived) You can look up more data about a backend by joining pg_locks to pg_stat_activity, using the PID (I think it's called procpid on one view and pid on the other). Hi, But pg_stat_activity joined with pg_locks only give me informations about the lock itself. Realy, I want a (possible) simple information: Who is locking me ? Today, I *presume* this information by manually search the pg_locks: 1) Search for the locked tables 2) Search for all lock GRANTED to this tables 3) Generally, the older PID is the locker Not so smart, I think. :) Best regards, Alexandre ---(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] Anyone using "POSIX" time zone offset capability?
What about time zones like Tehran (GMT+3:30), Kabul (GMT+4:30), Katmandu (GMT+5:45) and other non-cardinal-hour GMT offsets? Is this handled in some *documented* way already? -- Brandon Aiken CS/IT Systems Engineer -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Monday, October 16, 2006 6:06 PM To: pgsql-hackers@postgreSQL.org; pgsql-general@postgreSQL.org Subject: [GENERAL] Anyone using "POSIX" time zone offset capability? While trying to clean up ParseDateTime so it works reliably with full timezone names, I found out about a "feature" that so far as I can tell has never been documented except in comments in datetime.c. The datetime input code tries to recognize what it calls "POSIX time zones", which are timezone abbreviations followed by an additional hour/minute offset: /* DecodePosixTimezone() * Interpret string as a POSIX-compatible timezone: * PST-hh:mm * PST+h * PST * - thomas 2000-03-15 However this doesn't actually work in all cases: regression=# select '12:34:00 PDT+00:30'::timetz; timetz 12:34:00-07:30 (1 row) regression=# select '12:34:00 PDT-00:30'::timetz; ERROR: invalid input syntax for type time with time zone: "12:34:00 PDT-00:30" (The behavior varies depending on which PG release you try it with, but I can't find any that produce the expected result for a negative fractional-hour offset.) This syntax is ambiguous against some full timezone names present in the zic database, such as "GMT+0", and it's also responsible for a number of really ugly special cases in the datetime parser. In view of the fact that it's never entirely worked and never been documented, I'm inclined to take it out. Comments? Is anyone actually using this? regards, tom lane ---(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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Anyone using "POSIX" time zone offset capability?
While trying to clean up ParseDateTime so it works reliably with full timezone names, I found out about a "feature" that so far as I can tell has never been documented except in comments in datetime.c. The datetime input code tries to recognize what it calls "POSIX time zones", which are timezone abbreviations followed by an additional hour/minute offset: /* DecodePosixTimezone() * Interpret string as a POSIX-compatible timezone: * PST-hh:mm * PST+h * PST * - thomas 2000-03-15 However this doesn't actually work in all cases: regression=# select '12:34:00 PDT+00:30'::timetz; timetz 12:34:00-07:30 (1 row) regression=# select '12:34:00 PDT-00:30'::timetz; ERROR: invalid input syntax for type time with time zone: "12:34:00 PDT-00:30" (The behavior varies depending on which PG release you try it with, but I can't find any that produce the expected result for a negative fractional-hour offset.) This syntax is ambiguous against some full timezone names present in the zic database, such as "GMT+0", and it's also responsible for a number of really ugly special cases in the datetime parser. In view of the fact that it's never entirely worked and never been documented, I'm inclined to take it out. Comments? Is anyone actually using this? regards, tom lane ---(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] A query planner that learns
Alvaro Herrera wrote: Hasn't IBM release a pile of it's patents for use (or at least stated they won't sue) to OSS projects? If so, is this patent covered by that "amnesty"? This is useless as a policy, because we have plenty of companies basing their proprietary code on PostgreSQL, which wouldn't be subject to the grant (EnterpriseDB, Command Prompt, Fujitsu, SRA). We do support them. More specifically, it is then up to the 3rd party (non-OSS) developers to clear the patents. It's not the PgSQL team's problem in this case (assuming it's the case at all). Simply ignoring patents because "there is a patent for everything" is a recipe for disaster. Companies like MS are running out of ways to tear open OSS and they are certainly not above (below?) suing the heck out of OSS projects for patent infringement. It has been said that unknowingly infringing a patent is much less problematic than knowingly doing same. We don't have the manpower to implement the whole Postgres without infringing a single patent, so the best approach is to refrain from researching possible patents applying to us in the first place. If people comes here and points at patents that we infringe or may infringe, it will cause much more (useless) work for hackers which then have to search alternative ways of doing the same things. "Plausible Deniability" and all that jazz. There is another truism though; "Ignorance of the law is no excuse". Besides, claiming ignorance doesn't keep you out of the courts in the first place. The people who would attack OSS applications generally have very, very deep pockets and can run a project out of money before the trial was over. They could do that non-the-less (SCO, hello?) but I still suggest NOT tempting fate. It's unfortunate that this legal mess causes the developers more headaches than they need, but it still can't be ignored, imho. What's needed is reform in the USPO. Call you congress (wo)man and complain, but don't flaunt the law; you will lose. I agree. However, I am not an US inhabitant in the first place, and bless my parents for that. Heck, I was even denied a visa -- twice. Please do us all a favor and write to your congresspeople. Heh, I'm not an American either, so I can't do anything but shake my head (and be equally glad that my own personal OSS program is here in Canada). American industry wonders why they are losing so many IT positions... It's such a difficult and unfriendly environment there for anyone but the biggest companies. Far too litigious. Madi ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] A query planner that learns
Madison Kelly wrote: > Alvaro Herrera wrote: > >Jochem van Dieten wrote: > >>Scott Marlowe wrote: > >>>While all the talk of a hinting system over in hackers and perform is > >>>good, and I have a few queries that could live with a simple hint system > >>>pop up now and again, I keep thinking that a query planner that learns > >>>from its mistakes over time is far more desirable. > >>>Is it reasonable or possible for the system to have a way to look at > >>>query plans it's run and look for obvious mistakes its made, like being > >>>off by a factor of 10 or more in estimations, and slowly learn to apply > >>>its own hints? > >>Technically it is very feasible. But I think you might want to check US > >>Patent 6,763,359 before you start writing any code. > > > >I think it would be a very good idea if you guys stopped looking at the > >US patent database. It does no good to anyone. There's no way we can > >avoid stomping on a patent or another -- there are patents for everything. > > Hasn't IBM release a pile of it's patents for use (or at least stated > they won't sue) to OSS projects? If so, is this patent covered by that > "amnesty"? This is useless as a policy, because we have plenty of companies basing their proprietary code on PostgreSQL, which wouldn't be subject to the grant (EnterpriseDB, Command Prompt, Fujitsu, SRA). We do support them. > Simply ignoring patents because "there is a patent for everything" is a > recipe for disaster. Companies like MS are running out of ways to tear > open OSS and they are certainly not above (below?) suing the heck out of > OSS projects for patent infringement. It has been said that unknowingly infringing a patent is much less problematic than knowingly doing same. We don't have the manpower to implement the whole Postgres without infringing a single patent, so the best approach is to refrain from researching possible patents applying to us in the first place. If people comes here and points at patents that we infringe or may infringe, it will cause much more (useless) work for hackers which then have to search alternative ways of doing the same things. > What's needed is reform in the USPO. Call you congress (wo)man and > complain, but don't flaunt the law; you will lose. I agree. However, I am not an US inhabitant in the first place, and bless my parents for that. Heck, I was even denied a visa -- twice. Please do us all a favor and write to your congresspeople. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] pg_locks: who is locking ?
Alexandre Arruda wrote: > Hi, > > My Database have a lot of locks not granted every moments in a day. > > Can I create a view that returns someting like this ? > > User Granted Table Who_is_locking_me PID > --- - - --- > joe f foo frank 1212 > jeff f foo frank 1313 > ann f foo frank 1414 > frank t foo > (...) > > (Or the locked transactions, if the table cold't be retrived) You can look up more data about a backend by joining pg_locks to pg_stat_activity, using the PID (I think it's called procpid on one view and pid on the other). -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: RES: [GENERAL] Dates rejected
"Carlos H. Reimer" <[EMAIL PROTECTED]> writes: > How can we explain the 01:00:00 hour that the to_date function returns for > date 15/10/2006? You haven't fixed your configuration and your machine is considering that you're in DST. Lots of machines here in Brazil that weren't updated / fixed by their administrators are showing this problem. -- Jorge Godoy <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] datatype preceded by underscore creates array
> On Mon, Oct 16, 2006 at 01:16:34PM -0700, George Pavlov wrote: > > Hmm, I am not sure I particularly like this behavior or the > > "ignore it" > > advice. Suppose someone makes a typo in his/her table > > definition: meant > > to create an int4 column but accidentally typed an underscore. You'd > > expect the statement to fail. Instead it doesn't fail but creates an > > unexpected datatype for the column. If undescore is a > > purposeful (rather > > than an accidental) SQL standard extension one would expect > > it to be (a) documented in some place like > > > > http://www.postgresql.org/docs/8.1/static/arrays.html#AEN5584, and (b) > > behaving more consistently (if _foo is a synonym for foo[] then all > > variations of "foo" should support it). > > Hmm, if someone typos to get "int8" instead of "int4" they get the > wrong datatype too, I don't know if that's an argument. The reason is > that all types need to have an identifier. I suppose they could be > called "pg_internal_array_type_for_int4", but for historical reasons > it's just _int4. yes, but int8 is a clearly documented while preceding certain "magic" datatype names with underscores is not. i really don't have much of a problem with this, but little things like this contribute to people coming from other DBMSs developing opinions that "this open-source stuff does weird, undocumented things" (not that commercial DBMSs don't do weird stuff :). sorry to belabor a minor point. george ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] datatype preceded by underscore creates array
On Mon, Oct 16, 2006 at 01:16:34PM -0700, George Pavlov wrote: > Hmm, I am not sure I particularly like this behavior or the "ignore it" > advice. Suppose someone makes a typo in his/her table definition: meant > to create an int4 column but accidentally typed an underscore. You'd > expect the statement to fail. Instead it doesn't fail but creates an > unexpected datatype for the column. If undescore is a purposeful (rather > than an accidental) SQL standard extension one would expect it to be (a) > documented in some place like > http://www.postgresql.org/docs/8.1/static/arrays.html#AEN5584, and (b) > behaving more consistently (if _foo is a synonym for foo[] then all > variations of "foo" should support it). Hmm, if someone typos to get "int8" instead of "int4" they get the wrong datatype too, I don't know if that's an argument. The reason is that all types need to have an identifier. I suppose they could be called "pg_internal_array_type_for_int4", but for historical reasons it's just _int4. It's deprecated, you're not encouraged to use it and if it were easy to get rid of it would have been done a long time ago. > Not sure what you mean--char(x) is not an oddity and it does have array > support: The types char, integer, real, etc are the SQL names for the types and they have special SQL incantations for them. The underlying types are actually called bpchar, int4 and float8. The latter have magic array types, the former don't. > create table a7 (b char(1)[]); > CREATE TABLE This is just another way of saying: create table a7 (b _bpchar) except that doesn't allow you to specify a length... Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
RES: RES: [GENERAL] Dates rejected
Hi,
The problem is related with the to_timestamp function that returns +1 hour
offset only for the date 15/10/2006. The 15th october is the first day of
our day light change.
template1=# select pg_catalog.to_timestamp('15/10/2006','dd/mm/') as
date;
date
2006-10-15 01:00:00-02
(1 row)
Why is this offset present only for date 15/10/2006 (DD/MM/YYY)?
Carlos
> -Mensagem original-
> De: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] nome de Tom Lane
> Enviada em: segunda-feira, 16 de outubro de 2006 16:27
> Para: [EMAIL PROTECTED]
> Cc: Andreas Kretschmer; Pgsql-General@Postgresql.Org
> Assunto: Re: RES: [GENERAL] Dates rejected
>
>
> "Carlos H. Reimer" <[EMAIL PROTECTED]> writes:
> > select to_date('16/10/2006','DD/MM/');
> >to_date
> > -
> > 2006-10-16 00:00:00
> > (1 row)
>
> Um... what have you done to to_date()? The standard version returns a
> date, not a timestamp:
>
> regression=# select to_date('15/10/2006','DD/MM/');
> to_date
>
> 2006-10-15
> (1 row)
>
>
> regards, tom lane
>
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faq
>
>
---(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] A query planner that learns
Alvaro Herrera wrote: Jochem van Dieten wrote: Scott Marlowe wrote: While all the talk of a hinting system over in hackers and perform is good, and I have a few queries that could live with a simple hint system pop up now and again, I keep thinking that a query planner that learns >from its mistakes over time is far more desirable. Is it reasonable or possible for the system to have a way to look at query plans it's run and look for obvious mistakes its made, like being off by a factor of 10 or more in estimations, and slowly learn to apply its own hints? Technically it is very feasible. But I think you might want to check US Patent 6,763,359 before you start writing any code. I think it would be a very good idea if you guys stopped looking at the US patent database. It does no good to anyone. There's no way we can avoid stomping on a patent or another -- there are patents for everything. Hasn't IBM release a pile of it's patents for use (or at least stated they won't sue) to OSS projects? If so, is this patent covered by that "amnesty"? Simply ignoring patents because "there is a patent for everything" is a recipe for disaster. Companies like MS are running out of ways to tear open OSS and they are certainly not above (below?) suing the heck out of OSS projects for patent infringement. What's needed is reform in the USPO. Call you congress (wo)man and complain, but don't flaunt the law; you will lose. Madi ---(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] datatype preceded by underscore creates array
> Yep, the array type is represented internally by prefixings an > underscore. It's mentioned somewhere in the docs, but you may as well > ignore it. Hmm, I am not sure I particularly like this behavior or the "ignore it" advice. Suppose someone makes a typo in his/her table definition: meant to create an int4 column but accidentally typed an underscore. You'd expect the statement to fail. Instead it doesn't fail but creates an unexpected datatype for the column. If undescore is a purposeful (rather than an accidental) SQL standard extension one would expect it to be (a) documented in some place like http://www.postgresql.org/docs/8.1/static/arrays.html#AEN5584, and (b) behaving more consistently (if _foo is a synonym for foo[] then all variations of "foo" should support it). > "int4" is the actual type name, "integer" is the sql standard name. > PostgreSQL displays SQL compliant output where possible. _int simply > doesn't exist, and oddities like (3) after the char does have array > support at all... Not sure what you mean--char(x) is not an oddity and it does have array support: create table a7 (b char(1)[]); CREATE TABLE George ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] pg_locks: who is locking ?
Hi, My Database have a lot of locks not granted every moments in a day. Can I create a view that returns someting like this ? UserGranted Table Who_is_locking_me PID --- - - --- joe f foo frank 1212 jefff foo frank 1313 ann f foo frank 1414 frank t foo (...) (Or the locked transactions, if the table cold't be retrived) pg_locks view does not give me WHO is locking... Best regards, Alexandre ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] A query planner that learns
Jochem van Dieten wrote: I think you might want to check US Patent 6,763,359 before you start writing any code. http://tinyurl.com/yzjdve - John D. Burger MITRE ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] A query planner that learns
Jochem van Dieten wrote: > Scott Marlowe wrote: > >While all the talk of a hinting system over in hackers and perform is > >good, and I have a few queries that could live with a simple hint system > >pop up now and again, I keep thinking that a query planner that learns > >from its mistakes over time is far more desirable. > > > >Is it reasonable or possible for the system to have a way to look at > >query plans it's run and look for obvious mistakes its made, like being > >off by a factor of 10 or more in estimations, and slowly learn to apply > >its own hints? > > Technically it is very feasible. But I think you might want to check US > Patent 6,763,359 before you start writing any code. I think it would be a very good idea if you guys stopped looking at the US patent database. It does no good to anyone. There's no way we can avoid stomping on a patent or another -- there are patents for everything. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] calling two shared objects in one single function call
Hi,Can two different shared objects be loaded one after the other in the same function so that the second one starts its execution right after the first one finishes.~Harpreet
Re: [GENERAL] old Pg interface
On Mon, 16 Oct 2006, Oleg Bartunov wrote: I added some compatibility functions and it worked with new PostgreSQL. Sorry, I got confused :) I added them to wdb interface Let me know if you need it Oleg On Mon, 16 Oct 2006, Brandon Metcalf wrote: I'm currently using version 1.9.0 of the old Pg interface with PostgreSQL 8.0.3. Our code needs to be updated to use DBI/DBD::Pg, but we need to upgrade PostgreSQL before this is going to happen. Does anyone know of any issues with continuing to use the old Pg interface with newer versions of PostgreSQL? Thanks. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] A query planner that learns
Scott Marlowe wrote: While all the talk of a hinting system over in hackers and perform is good, and I have a few queries that could live with a simple hint system pop up now and again, I keep thinking that a query planner that learns from its mistakes over time is far more desirable. Is it reasonable or possible for the system to have a way to look at query plans it's run and look for obvious mistakes its made, like being off by a factor of 10 or more in estimations, and slowly learn to apply its own hints? Technically it is very feasible. But I think you might want to check US Patent 6,763,359 before you start writing any code. It seems to me the first logical step would be having the ability to flip a switch and when the postmaster hits a slow query, it saves both the query that ran long, as well as the output of explain or explain analyze or some bastardized version missing some of the inner timing info. Even just saving the parts of the plan where the planner thought it would get 1 row and got instead 350,000 and was using a nested loop to join would be VERY useful. I could see something like that eventually evolving into a self tuning system. I think it would be a good start if we can specify a log_selectivity_error_threshold and if estimates are more then that factor off, the query, parameters and planner estimates get logged for later analysis. That would be driven entirely by selectivity estimates and not (estimated) cost since cost is influenced by outside factors such as other processes competing for resources. If a system for statistical hints emerges from the current discussion we would indeed have the input to start tuning the selectivity estimations. Jochem ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] old Pg interface
I added some compatibility functions and it worked with new PostgreSQL. Let me know if you need it Oleg On Mon, 16 Oct 2006, Brandon Metcalf wrote: I'm currently using version 1.9.0 of the old Pg interface with PostgreSQL 8.0.3. Our code needs to be updated to use DBI/DBD::Pg, but we need to upgrade PostgreSQL before this is going to happen. Does anyone know of any issues with continuing to use the old Pg interface with newer versions of PostgreSQL? Thanks. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] datatype preceded by underscore creates array
On Mon, Oct 16, 2006 at 12:33:35PM -0700, George Pavlov wrote: > Is there any special meaning to preceding a datatype (or at least some > datatypes) in a table or function definition by underscore that is a > synonym for an array? I can't see it documented anywhere. Below are some > examples. The other question is why "_int4" parses to int[], but "_int" > does not, etc. This is on PostgreSQL 8.1.3 Linux. Yep, the array type is represented internally by prefixings an underscore. It's mentioned somewhere in the docs, but you may as well ignore it. "int4" is the actual type name, "integer" is the sql standard name. PostgreSQL displays SQL compliant output where possible. _int simply doesn't exist, and oddities like (3) after the char does have array support at all... Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] old Pg interface
On Mon, Oct 16, 2006 at 12:15:36PM -0500, Brandon Metcalf wrote: > I'm currently using version 1.9.0 of the old Pg interface with > PostgreSQL 8.0.3. Our code needs to be updated to use DBI/DBD::Pg, > but we need to upgrade PostgreSQL before this is going to happen. > Does anyone know of any issues with continuing to use the old Pg > interface with newer versions of PostgreSQL? I have a lot of code that still uses the old Pg interface. It works fine against newer versions. There's no requirement to change. If it ain't broke, don't fix it. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
[GENERAL] datatype preceded by underscore creates array
Is there any special meaning to preceding a datatype (or at least some
datatypes) in a table or function definition by underscore that is a
synonym for an array? I can't see it documented anywhere. Below are some
examples. The other question is why "_int4" parses to int[], but "_int"
does not, etc. This is on PostgreSQL 8.1.3 Linux.
gp=> create table a1 (b _int4);
CREATE TABLE
gp=> \d a1
Table "mnp.a1"
Column | Type| Modifiers
+---+---
b | integer[] |
gp=> create table a2 (b _char);
CREATE TABLE
gp=> \d a2
Table "mnp.a2"
Column | Type | Modifiers
+--+---
b | "char"[] |
gp=> create table a3 (b _int);
ERROR: type "_int" does not exist
gp=> create table a4 (b _char(1));
ERROR: syntax error at or near "(" at character 25
LINE 1: create table a4 (b _char(1));
^
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] function for current date-time
am Mon, dem 16.10.2006, um 14:56:27 -0400 mailte Harpreet Dhaliwal folgendes: > Hi, > I have a timestamp field in my table and want to set a default value of > current > date/time for it. > What should i enter as its default value? is there any function like now() in > postgres? Yes, now() ;-) test=# create table foo (id int, ts timestamp default now()); CREATE TABLE test=# insert into foo (id, ts) values (1, default); INSERT 0 1 test=# select * from foo; id |ts +--- 1 | 2006-10-16 21:18:15.75039 (1 row) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(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
RES: RES: [GENERAL] Dates rejected
Hi Tom,
You are right, I´ve discovered that the to_date was changed to return a
timestamp, the original function is returning the right values.
The to_date I´ve found:
CREATE OR REPLACE FUNCTION PUBLIC.TO_DATE(text, text) RETURNS TIMESTAMP
AS '
BEGIN
RETURN pg_Catalog.TO_TIMESTAMP($1,$2);
END;
' language 'plpgsql';
I don´t know why they have changed it but anyway why is this changed
function returning 01:00:00 in the hour field only for the date 15/10/2006
(DD/MM/)?
It started happening in the first day when Linux has changed to the day
light time (15/10/2006).
Thanks in advance!
Carlos
> -Mensagem original-
> De: Tom Lane [mailto:[EMAIL PROTECTED]
> Enviada em: segunda-feira, 16 de outubro de 2006 16:27
> Para: [EMAIL PROTECTED]
> Cc: Andreas Kretschmer; Pgsql-General@Postgresql.Org
> Assunto: Re: RES: [GENERAL] Dates rejected
>
>
> "Carlos H. Reimer" <[EMAIL PROTECTED]> writes:
> > select to_date('16/10/2006','DD/MM/');
> >to_date
> > -
> > 2006-10-16 00:00:00
> > (1 row)
>
> Um... what have you done to to_date()? The standard version returns a
> date, not a timestamp:
>
> regression=# select to_date('15/10/2006','DD/MM/');
> to_date
>
> 2006-10-15
> (1 row)
>
>
> regards, tom lane
>
>
---(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] function for current date-time
On Mon, 2006-10-16 at 14:56 -0400, Harpreet Dhaliwal wrote: > Hi, > I have a timestamp field in my table and want to set a default value > of current date/time for it. > What should i enter as its default value? is there any function like > now() in postgres? > > Thanks, > ~Harpreet test=# select now(); now --- 2006-10-16 15:03:13.174644-04 (1 row) ---(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] old Pg interface
On Oct 16, 2006, at 1:15 PM, Brandon Metcalf wrote: Does anyone know of any issues with continuing to use the old Pg interface with newer versions of PostgreSQL? it is just a rather thin glue layer on top of the libpq interface, so it should continue to work just as any libpq app would continue to work. smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] RES: Dates rejected
On Oct 16, 2006, at 1:08 PM, Carlos H. Reimer wrote: How can we explain the 01:00:00 hour that the to_date function returns for date 15/10/2006? does your timezone change from summer time to winter time (daylight savings, etc.) on that date? smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] function for current date-time
Harpreet Dhaliwal wrote: Hi, I have a timestamp field in my table and want to set a default value of current date/time for it. What should i enter as its default value? is there any function like now() in postgres? Thanks, ~Harpreet http://www.postgresql.org/docs/8.1/interactive/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] difference b/w text and varchar datatypes
> can you please provide me the link for the same. > thanks The faq is under the documentation link of the PostgreSQL homepage. http://www.postgresql.org/docs/faq/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] difference b/w text and varchar datatypes
> Just wanted to know how would it make a difference if i use text datatype > instead of varchar. Taken from: http://www.postgresql.org/docs/8.1/interactive/datatype-character.html " If character varying is used without length specifier, the type accepts strings of any size. The latter is a PostgreSQL extension. In addition, PostgreSQL provides the text type, which stores strings of any length. Although the type text is not in the SQL standard, several other SQL database management systems have it as well. ... Tip: There are no performance differences between these three types, apart from the increased storage size when using the blank-padded type. While character(n) has performance advantages in some other database systems, it has no such advantages in PostgreSQL. In most situations text or character varying should be used instead. " Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] function for current date-time
Hi,I have a timestamp field in my table and want to set a default value of current date/time for it.What should i enter as its default value? is there any function like now() in postgres?Thanks,~Harpreet
Re: [GENERAL] difference b/w text and varchar datatypes
can you please provide me the link for the same.thanksOn 10/16/06, Bruce Momjian <[EMAIL PROTECTED]> wrote: Harpreet Dhaliwal wrote:> Hi,> Just wanted to know how would it make a difference if i use text datatype > instead of varchar.See FAQ item.-- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Re: [GENERAL] difference b/w text and varchar datatypes
Harpreet Dhaliwal wrote: > Hi, > Just wanted to know how would it make a difference if i use text datatype > instead of varchar. See FAQ item. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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] difference b/w text and varchar datatypes
Hi,Just wanted to know how would it make a difference if i use text datatype instead of varchar.Thanks,~Harpreet
Re: [GENERAL] Fast backup/restore
On Mon, 2006-10-16 at 16:29 +0530, Gandalf wrote: > I am looking for a *fast* backup/restore tools for Postgres. I've > found the current used tools pg_dump and pg_restore to be very slow on > large databases (~30-40GB). Restore takes time in the tune of 6 hrs on > a Linux, 4 proc, 32 G RAM machine which is not acceptable. > > I am using "pg_dump -Fc" to take backup. I understand binary > compression adds to the time, but there are other databases (like DB2) > which take much less time on similar data sizes. > > Are there faster tools available? > http://www.postgresql.org/docs/8.1/static/backup-online.html With that backup system, you can backup with normal filesystem-level tools (e.g. tar) while the database is online. Make sure to backup the remaining active WAL segments. Those are necessary for the backup to be complete. This step will be done automatically in 8.2. If your filesystem has snapshot capability, you have nothing to worry about. Just snapshot the fs and backup the data directory plus any WAL segments and tablespaces. Regards, Jeff Davis ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: RES: [GENERAL] Dates rejected
"Carlos H. Reimer" <[EMAIL PROTECTED]> writes:
> select to_date('16/10/2006','DD/MM/');
>to_date
> -
> 2006-10-16 00:00:00
> (1 row)
Um... what have you done to to_date()? The standard version returns a
date, not a timestamp:
regression=# select to_date('15/10/2006','DD/MM/');
to_date
2006-10-15
(1 row)
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Backup DB not getting connected
i changed my connection string as follows keeping newDB in double quotesEXEC SQL CONNECT TO tcp:postgresql://192.168.1.1:5432/"newDB"Unfortunately these doulbe quotes lead to syntax error.Can you tell me what is the right syntax for the same. ThanksOn 10/13/06, Tom Lane <[EMAIL PROTECTED]> wrote: "J S B" <[EMAIL PROTECTED]> writes:> well, newDB is the name of the database and that what I'm tryin to connect.> I'm tryin to keep it the same case in ECPG code as it is in the Database. > Do u mean to say that combination of upper and lower case is not allowed?No, I mean to say that names will get downcased in some situations ifnot double-quoted. I wonder whether your database is *really* named "newDB" and not "newdb" ... if it is, you probably need double quotes inyour ECPG call, and if it isn't, you probably need to spell it as newdbnot newDB in ECPG.regards, tom lane
[GENERAL] old Pg interface
I'm currently using version 1.9.0 of the old Pg interface with PostgreSQL 8.0.3. Our code needs to be updated to use DBI/DBD::Pg, but we need to upgrade PostgreSQL before this is going to happen. Does anyone know of any issues with continuing to use the old Pg interface with newer versions of PostgreSQL? Thanks. -- Brandon ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
RES: [GENERAL] Dates rejected
Hi,
I don´t know why the developers build in this way... but let me change a
little bit my question.
I´ve executed tree to_date functions but they give a strange answer for date
16/10/2006 (DD/MM/).
select to_date('16/10/2006','DD/MM/');
to_date
-
2006-10-16 00:00:00
(1 row)
select to_date('15/10/2006','DD/MM/');
to_date
-
2006-10-15 01:00:00
(1 row)
select to_date('14/10/2006','DD/MM/');
to_date
-
2006-10-14 00:00:00
(1 row)
How can we explain the 01:00:00 hour that the to_date function returns for
date 15/10/2006?
Thank you!
Carlos
> -Mensagem original-
> De: Andreas Kretschmer,,, [mailto:[EMAIL PROTECTED] nome de
> Andreas Kretschmer
> Enviada em: segunda-feira, 16 de outubro de 2006 13:41
> Para: pgsql-general@postgresql.org
> Cc: Carlos H. Reimer
> Assunto: Re: [GENERAL] Dates rejected
>
>
> Martijn van Oosterhout schrieb:
> > > create table tt_teste (datfis timestamp without time zone not null
> > > CHECK (datfis = trunc(datfis::timestamp without time zone)));
> >
> > What are you trying to do here? If you only want a date, why not just
> > use a date type?
>
> This is an other question ;-)
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect. (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly."(unknow)
> Kaufbach, Saxony, Germany, Europe. 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] query log corrupted-looking entries
"George Pavlov" <[EMAIL PROTECTED]> writes: > And yes, redirect_stderr = on. I have no definitive way of reproducing > it, just a vague one: "hit the server with lots of queries". Hmm. If the messages are less than PIPE_BUF bytes long (4096 bytes on Linux) then the writes are supposed to be atomic. Can you check whether the interspersal cases involve messages whose total length (all lines) exceeds 4K? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Real time query analyzer
Adrian Suciu <[EMAIL PROTECTED]> schrieb: > Hi everybody! > I ask you for your help on a problem I have. > I have a postgresql 7.4 running on a dual 4 GB RAM server, but I have some > VERY memory intense queries, that put processor up to 40%. I see all this > info in unix "top" command or ps -aux > Unfortunately they don't show me the query itsself. > > Is there a tool for postgres that can show me the queries and the time they > take and the amount of processor is consuming (as Oracle has) ? You can do some things: - set log_min_duration_statement = X to log all statments with a duration more than X ms. I'm not sure, if this possible with 7.4 - select * from pg_stat_activity; you should enable stats_command_string ;-) Now you can identify your expensive querys and anaylse this with EXPLAIN. 17:53 < akretschmer> ??explain 17:53 < rtfm_please> For information about explain 17:53 < rtfm_please> see http://techdocs.postgresql.org/oscon2005/robert.treat/OSCON_Explaining_Explain_Public.sxi 17:53 < rtfm_please> or http://www.postgresql.org/docs/current/static/sql-explain.html Read the links above to learn more about EXPLAIN. HTH, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly."(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(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] Real time query analyzer
Hi everybody! I ask you for your help on a problem I have. I have a postgresql 7.4 running on a dual 4 GB RAM server, but I have some VERY memory intense queries, that put processor up to 40%. I see all this info in unix "top" command or ps -aux Unfortunately they don't show me the query itsself. Is there a tool for postgres that can show me the queries and the time they take and the amount of processor is consuming (as Oracle has) ? I appreciate any help. Thank you, Adrian ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Dates rejected
Martijn van Oosterhout schrieb: > > create table tt_teste (datfis timestamp without time zone not null > > CHECK (datfis = trunc(datfis::timestamp without time zone))); > > What are you trying to do here? If you only want a date, why not just > use a date type? This is an other question ;-) Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly."(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Dates rejected
On Mon, Oct 16, 2006 at 12:22:04PM -0200, Carlos H. Reimer wrote: > Hi, > > We´ve a simple insert that is not working. The strange thing is that all > kind of date are working with the exception of 15/10 (DD/MM) dates. > > create table tt_teste (datfis timestamp without time zone not null > CHECK (datfis = trunc(datfis::timestamp without time zone))); What are you trying to do here? If you only want a date, why not just use a date type? Have you tried evaluating the expression yourself? Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Dates rejected
Carlos H. Reimer <[EMAIL PROTECTED]> schrieb: > > Hi, > > WeŽve a simple insert that is not working. The strange thing is that all kind > of date are working with the exception of 15/10 (DD/MM) dates. > > create table tt_teste (datfis timestamp without time zone not null > CHECK (datfis = trunc(datfis::timestamp without time zone))); ^ we don't have such a function, perhaps date_trunc()? Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly."(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Dates rejected
Hi,
We´ve a simple
insert that is not working. The strange thing is that all kind of date are
working with the exception of 15/10 (DD/MM) dates.
create table tt_teste (datfis timestamp without
time zone not null CHECK (datfis = trunc(datfis::timestamp without time
zone)));
INSERT INTO tt_teste (datfis) VALUES (
to_date('15/10/2006','DD/MM/') );
ERROR: new row for relation "tt_teste"
violates check constraint "tt_teste_datfis_check"
I´ll
appreciate any help!
Thanks in
advance!
Carlos
Reimer
Re: [GENERAL] ERROR: current transaction is aborted, commands ignored until end of transaction block
On Mon, Oct 16, 2006 at 06:54:31AM -0700, DXScale452 wrote: > if if i swap the ordering of any of these tables the second in the list > always fail... > > HERE is the syntax of the statements before the fail > -_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_ > SELECT value FROM table1 > INSERT INTO table1 (numeric_type, value_description) VALUES (0,'Description') > SELECT value FROM table2 > ERROR: current transaction is aborted, commands ignored until end of > transaction block > -_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_ There must be another error you're not displaying. If you have a look at the server log file, what do you see? Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
[GENERAL] ERROR: current transaction is aborted, commands ignored until end of transaction block
OS: Linux CentOS 4.4PostgreSQL Version: 8.1.4Error: current transaction is aborted, commands ignored until end of transaction blockI have seen this error in your forums... but in those cases... the values being inserted were erronious therefore the transaction abortedI cannot give the exact details in the code for the sake of confidentiality.Here is the best example i can give(ALGORITHMIC
FORM)--int main(){ int Ret = 0; Ret = Connection_function(connection_object); Check_Return(Ret); // Exit On Any Errors Ret = Transaction("BEGIN"); Check_Return(Ret); // Check_Return() will exit on any errors// Error checking is done after each select or insert Ret = Query_SELECT(table1); // if value found in table... dont insert else... Insert into table Check_Return(Ret); Ret = Query_SELECT(table2); // if value found in table... dont insert else... Insert into table
Check_Return(Ret); Ret = Query_SELECT(table3); // if value found in table... dont insert else... Insert into table Check_Return(Ret); Ret = Query_SELECT(table4); // if value found in table... dont insert else... Insert into table Check_Return(Ret); Ret = Transaction("COMMIT"); Check_Return(Ret); return 0;}That is basically it sorry if its long and if there are any stupid mistakesthis is the quick and dirty versionHere is my problemWhen i'm inside the transaction blockIt always fails on the second
tableduring a select query...even if i output the exact syntax of the insert statement and follow the exact order of insertingThen manually insert it into the database With a transaction block it doesnt fail...something to do with the database integration in C++ ?if if i swap the ordering of any of these tables the second in the list always fail...HERE is the syntax of the statements before the fail-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_SELECT value FROM table1INSERT INTO table1 (numeric_type, value_description) VALUES (0,'Description')SELECT value FROM table2ERROR: current transaction is aborted, commands ignored until end of transaction block-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_even if i placed table2 first and then table1 table1 would fail (the second select statement always fails)the tables are empty to begin with... so in
effect... there should be a statement like this instead of the ERRORAt least this is what i expect...-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_SELECT value FROM table1
INSERT INTO table1 (numeric_type, value_description) VALUES (0,'Description')
SELECT value FROM table2INSERT INTO table2 (numeric_type, value_description) VALUES (0,'Description')-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_If i remove the transaction block everything works without a problemthese statements dont have bugs... they have been thoroughly checkedalso considering they work without a transaction block tells me the statements are okPlease Help could this be a bug ?Thanks in advance,Regards,Scale
Re: [GENERAL] more anti-postgresql FUD
On 10/16/06, Alban Hertroys <[EMAIL PROTECTED]> wrote: Merlin Moncure wrote: > for server side browsing use cursors or a hybrid pl/pgqsl loop. for > client side, browse fetching relative to the last key: > > select * from foo where p > p1 order by p limit k; This does require some way for the client to keep a single transaction open. If this kind of query is performed by a web application (as is often the case), the "client" is the server side web script engine, and not all of those beasts are capable of keeping a transaction open across pages (PHP comes to mind). This combined with expensive (complex) queries is regularly a pain. Server-side browsing requires transactions so is unsuitable for certain types of web enviroments. However client-side following as I described as not...it is the right and proper way to solve this problem . It's also why the sql row-wise comparion is so important, because it provides an easy way to do this with table with mutiple part keys. merlin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Fast backup/restore
Gandald,have a look athttp://momjian.us/main/writings/pgsql/administration.pdfpage 44ffThere are descriptions how to do database-backups at the speed of raw file system operations. Harald-- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b70197 Stuttgart0173/9409607-Python: the only language with more web frameworks than keywords.
[GENERAL] Fast backup/restore
I am looking for a *fast* backup/restore tools for Postgres. I've found the current used tools pg_dump and pg_restore to be very slow on large databases (~30-40GB). Restore takes time in the tune of 6 hrs on a Linux, 4 proc, 32 G RAM machine which is not acceptable. I am using "pg_dump -Fc" to take backup. I understand binary compression adds to the time, but there are other databases (like DB2) which take much less time on similar data sizes. Are there faster tools available? Thanks.
Re: [GENERAL] more anti-postgresql FUD
On Mon, 16 Oct 2006 11:05:33 +0200 Alban Hertroys <[EMAIL PROTECTED]> wrote: > This does require some way for the client to keep a single > transaction open. If this kind of query is performed by a web > application (as is often the case), the "client" is the server side > web script engine, and not all of those beasts are capable of > keeping a transaction open across pages (PHP comes to mind). > This combined with expensive (complex) queries is regularly a pain. But which scripting language in a web environment (read running under Apache) is able to keep transactions open across pages? -- Ivan Sergio Borgonovo http://www.webthatworks.it ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] exploiting features of pg to obtain polymorphism
On Fri, 13 Oct 2006 16:37:42 +0200 Karsten Hilbert <[EMAIL PROTECTED]> wrote: > On Thu, Oct 12, 2006 at 04:40:32PM +0200, Ivan Sergio Borgonovo > wrote: > > > Anyway it doesn't solve the problem of having lists that > > can contain different elements with same parent and maintain > > ref. integrity. > Only to some degree. > > You can put a unique constraint and a serial default on the > parent table (such as a primary key). Insertion on a child > table will fail if the key in question already exists in the > base table. It may have come from another child table. Is it really true? http://www.postgresql.org/docs/8.1/static/ddl-inherit.html " If we declared cities.name to be UNIQUE or a PRIMARY KEY, this would not stop the capitals table from having rows with names duplicating rows in cities. And those duplicate rows would by default show up in queries from cities. In fact, by default capitals would have no unique constraint at all, and so could contain multiple rows with the same name. You could add a unique constraint to capitals, but this would not prevent duplication compared to cities. " > Updating the base table updates all the relevant child > tables, too. Delete will extend from base to child tables, > too. That way I'll have a unique serial across all the child > tables. I just need to take care to not use ONLY on > update/delete on the base table or to INSERT into the base > table directly (the latter isn't really harmful to the > issue, however). It would be nice if at least delete fired by triggers on the parent worked. But it doesn't since rows inserted in children don't get inserted in parents (that's OK on a OO perspective). But while the behaviour seems correct under an OO point of view it looks "unexpected". What seems to happen using inherits is you're creating *different* tables that share serials. > > Audit tables have their own pk/fk relationships and their > > triggers but according to my knowledge they won't be considered > > unless you operate on those table directly. If you operate on the > > data tables those triggers pk/fk won't be seen. > True. But I still get the unique pks since I don't operate > on them directly. Eventually, PG will enforce those > constraints, too. You get a serial in children, not uniqueness. I wrote some example code here: http://www.webthatworks.it/drupal/2006/10/db/postgresql_inheritance_surprises that shows you don't get any of the above with the exception of serial. Corrections are welcome. > > even if I've the suspect the code is not complete enough > > to implement the features > Yes. Eventually it is going to be something like Veil. Or > rather, I suppose it will *be* (as in use) Veil. I didn't understand. Are you referring to this? http://veil.projects.postgresql.org/curdocs/index.html > > There is one point of contact between what I did already > > and what I would like to do but I still haven't had a good > > idea how to implement it. The use of metadata. But > > definitively I can't see polymorphism in your use of > > inheritance. > Surely not to the extent a C++ programmer would hope for. That was my fault. -- Ivan Sergio Borgonovo http://www.webthatworks.it ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] more anti-postgresql FUD
Merlin Moncure wrote: On 10/13/06, Roman Neuhauser <[EMAIL PROTECTED]> wrote: SELECT * FROM TABLE ORDER BY pk LIMIT 10 OFFSET N; using offset to walk a table is extremely poor form because of: * poor performance * single user mentality * flat file mentality databases are lousy at this becuase they inheritly do not support abolute addressing of data -- nore should they, beause this is not what sql is all about. in short, 'offset' is a hack, albeit a useful one in some cases, but dont gripe when it doesn't deliver the goods. for server side browsing use cursors or a hybrid pl/pgqsl loop. for client side, browse fetching relative to the last key: select * from foo where p > p1 order by p limit k; This does require some way for the client to keep a single transaction open. If this kind of query is performed by a web application (as is often the case), the "client" is the server side web script engine, and not all of those beasts are capable of keeping a transaction open across pages (PHP comes to mind). This combined with expensive (complex) queries is regularly a pain. The alternative solution of storing the query results in a temporary table suffers from the same problem (the transaction is gone after the first page). I believe, as a result of this, it is not uncommon to pass the primary key id's of all results on in a hidden field, so they are available for quick querying on proceeding pages. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] A query planner that learns
Jim C. Nasby wrote: On Thu, Oct 12, 2006 at 05:39:20PM -0500, Scott Marlowe wrote: It may well be that by first looking at the data collected from problems queries, the solution for how to adjust the planner becomes more obvious. Yeah, that would be useful to have. The problem I see is storing that info in a format that's actually useful... and I'm thinking that a logfile doesn't qualify since you can't really query it. You'd need something that contains the query plan (obviously), but also all relevant information about the underlying data model and data distribution. Some meta-data, like the version of PostgreSQL, is probably required as well. The current statistics contain some of this information, but from reading this list I know that that's rarely enough information to determine an error made by the planner. Regards, -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(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] techdocs.2 how long has this be around?
> Is this new? > Who ever spent the time to do this, thanks for the effort. > Having the content organized this way makes it easy to find > specific reading material. Are you talking about http://www.postgresql.org/docs/techdocs? If so, it's been around for quite a whilen ow, but we're still not finished in migrating all the contents over from the old site. As for the organization, I beleive you should thank Robert Treat who's done most of the migration. //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
