Re: [HACKERS] Windows installer bugs (was: [BUGS] BUG #2374: Installation
Magnus Hagander wrote: Now, it would certainly help if more people could actually help in *answering* the bugs/questions that are posted :), but that's a different question... //Magnus I have created a very nice installer using Inno setup and it is about 50 times easier to code for than the current cryptic WIX setup. Why don't you have a look? Here is the source with a bsd license: http://www.amsoftwaredesign.com/downloads/pg_installer_setup.zip All the create user and runas stuff is done using API commands via a Delphi or Free Pascal DLL(not tested but should work) It does not use the RunAs service at all and handles the initDB. You could also use a C or C++ dll just as easily. Just reminding everyone it's out there. -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] First Aggregate Funtion?
Has there ever been any talk of adding a first aggregate function? It would make porting from Oracle and Access much easier. Or is there something in the contrib modules that I might have missed? Thanks, -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(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: [HACKERS] control pg_hba.conf via SQL
Not sure about the luxury - iirc there was some change in the format of pg_hba.conf anyway over the time and beside pgadmin3 I dont see many tools to edit this file (apart from the usual text editor ;) Just a FYI, PG Lightning Admin edits the pg_hba.conf as well as the postgresql.conf remotely, and does it pretty much the same way as pgAdmin III. -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Did this work in earlier version of Postgres?
Peter Eisentraut wrote: There has never been a type named double in PostgreSQL. The type name mandated by the SQL standard is double precision, and PostgreSQL supports that. Ok, Thanks for clearing that up for me :-) Maybe it was pgAdmin that did the substitution. Thanks, Tony ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Did this work in earlier version of Postgres?
I could have swore that this worked in earlier releases of Postgresql i.e. 7.4. CREATE TABLE public.test ( junk double NOT NULL, CONSTRAINT junk_pkey PRIMARY KEY (junk) )WITHOUT OIDS; Now it gives a error that type double does not exist. During the summer of 2004 I ported a large Firebird database to 7.x and firebird uses the term double which in PG is a float8 I believe. Anyway i was able to just paste the Firebird ddl in to the query editor and the server would substitute the correct PG native type. varchar works, how come double does not? Thanks, Tony ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] New project launched : PostgreSQL GUI Installer for
Devrim GUNDUZ wrote: Have you looked at AutoPackage? http://autopackage.org screen shots. http://autopackage.org/gallery.html Has a GUI wizard if X windows is available and a command line wizard if no X is available. Using autopackage is similar to using MSI,Wise,Inno etc on Windows. Later, -- Tony Caduto AM Software Design Home of PG Lightning Admin for Postgresql http://www.amsoftwaredesign.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
[HACKERS] win32 server question
Hi, Does anyone know how the win32 server checks if the user is non priveleged? Does it just check if the user is in the admin or power user group? Thanks, -- Tony Caduto AM Software Design Home of PG Lightning Admin for Postgresql http://www.amsoftwaredesign.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: [HACKERS] Question about postgresql-8.1.2-1-binaries-no-installer.zip(win32)
Magnus Hagander wrote: It's used for ecpg, IIRC, when compiled in thread-safe mode. //Magnus Thanks Magnus, Here is another question for you. Is it documented anywhere or does someone know what is the bare minimum requirements to run the server on a production box? I want to create the litest possible setup for use in my IM server (Lightning Messenger), and eliminate any unneeded files so I can have the smallest setup I can get. I already have a complete working setup built with Inno setup(it's 4.8 mb), now I just need to get it as lite as posssible. Thanks, -- Tony Caduto AM Software Design Home of PG Lightning Admin for Postgresql http://www.amsoftwaredesign.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Question about postgresql-8.1.2-1-binaries-no-installer.zip(win32)
Hi, I noticed that when I install via the msi setup there is a extra DLL in the bin directory called pthreadGC2.dll. (Posix thread library for windows) This dll is not in the postgresql-8.1.2-1-binaries-no-installer.zip file. Postgresql seems to run fine without out when I do a manual install using the zip file. If this is not used for the server what is it used for? Thanks, -- Tony Caduto AM Software Design Home of PG Lightning Admin for Postgresql http://www.amsoftwaredesign.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Offer for PG Developers/Hackers
Hi, I want to give something back(I would give a donation but sales are poor :-( ,so I am offering to any verified Postgresql developer(by verified I mean your name shows up on this list a LOT ) a free copy of PG Lightning Admin. I know most of you guys don't use windows, but if you do your welcome to a copy. There are no strings attached and you don't have to do anything other than keeping the setup password to yourself. Just let me know via email. Thanks, -- Tony Caduto AM Software Design Home of PG Lightning Admin for Postgresql http://www.amsoftwaredesign.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Question about Postgresql time fields(possible bug)
That's not really the point. The ISO 8601 standard allows midnight to be expressed as 00:00:00 or 24:00:00 to enable you to tell which midnight is being referred to (ie. The beginning or the end of the day). PostgreSQL allows you to make use of that part of the standard, and as admin tool authors I think we should honour what it allows, provided it's not blatantly non-standard. It's up to the user to decide whether or not they actually make use of the facility. For most database applications there is no practical reason to be using a time of 24:00:00(at least none I can think of) and Delphi does not allow a timestamp to contain 24 in the hours position. I have reported it to my database component vendor, maybe they will address it, maybe not. Doing a little research I found that some DBs support it (DB2 for example) and others do not. Since I am targeting mostly windows users with my product, I guess for now I will just allow it to be flagged as invalid. Later, -- Tony Caduto AM Software Design Home of PG Lightning Admin for Postgresql http://www.amsoftwaredesign.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Question about Postgresql time fields(possible bug)
Hi, I just noticed today that Postgresql accepts a value of 24:00:00, this is for sure not correct as there is no such thing as 24:00:00 PG Admin III will display this value just fine which is also incorrect, PG Lightning Admin catches it as a invalid time, but shouldn't there be some validation of times and dates at the server level? There are people who are using PG Admin III and they don't even know they have bogus dates and times in their databases. Thanks, Tony ---(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: [HACKERS] Comments from a Firebird user via Borland Newsgroups.
Tom Lane wrote: http://candle.pha.pa.us/main/writings/pgsql/sgml/sql-set-transaction.html http://candle.pha.pa.us/main/writings/pgsql/sgml/transaction-iso.html It's a bit amusing that this person is dissing us for not having REPEATABLE READ, when what he actually seems to want is SERIALIZABLE (which we've had since 1999). Certainly REPEATABLE READ does *not* guarantee a "stable view of data during one transaction" --- see the discussion of phantom reads in the second link given above. regards, tom lane Tom, This is what the firebird guy said: > Serializable is stricter and somehwat unusable in a multi-user, loaded > database, because only one transaction can run at any time. Let's say you > would have one long running serializable transaction encapsulating a > reporting query, this will cause other transactions to wait. > > There is a pretty good paper on discussing why it was a somewhat bad idea to > describe transaction isolation levels in terms of phenomena in the SQL > standard. This paper also describes transaction isolation levels for MVCC > databases. The paper is from 1995. http://www.cs.duke.edu/~junyang/courses/cps216-2003-spring/papers/berenson-etal-1995.pdf > > SNAPSHOT in Firebird isn't a SQL standard compliant REPEATBLE READ either. > SNAPSHOT in Firebird is between REPEATABLE READ and SERIALIZABLE, but > without blocking other transactions. Is this true? will SERIALIZABLE block all transactions on the whole server, or just on that one connection? Thanks, Tony ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Comments from a Firebird user via Borland Newsgroups.
simply better than PostgreSQL: Two-Phase commit (ok, that is gone with PG 8.1), but the second is a SNAPSHOT / REPEATABLE READ transaction isolation. I can't live without that when it comes having a stable view of data during one transaction, or did that change with 8.1? Is there now a SNAPHOST / REPEATBLE READ transaction isolation level available as well?> Just wondering what the PG take on this snapshot repeatable read stuff is. Tony ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Possible problem with pg_reload_conf() and view pg_settings
Andrew Dunstan wrote: Qingqing Zhou wrote: test=# LOG: received SIGHUP, reloading configuration files test=# select setting from pg_settings where name = 'constraint_exclusion'; setting - off (1 row) test=# select setting from pg_settings where name = 'constraint_exclusion'; setting - on (1 row) -- Seems that's due to delay of process SIGHUP ... What's the delay? 1s? 5? 10? cheers andrew ---(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 hmm, I waited for at least 1 minute after doing the reload and it was never updated. It seemed at the time that the only way to get a updated pg_settings view was to actually restart the server. I plan on doing some more testing on Saturday. Tony ---(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
[HACKERS] Possible problem with pg_reload_conf() and view pg_settings
Hi, I have been playing around with pg_reload_conf() and the pg_settings view. I understand that the pg_settings view, if updated, applies to the current session only. However I was under the impression that if I did a pg_reload_conf(), the pg_settings view would be updated at that time, but that does not seem to happen. I am running on win32, but the same thing happens on Linux. If I restart the Postgresql service then the pg_settings view contains the changes I made to the postgresql.conf file. Any ideas, does this seem like a possible bug? It just seems to me that pg_settings should be updated if a pg_reload_conf() is executed. Thanks, Tony ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] 8.04 and RedHat/CentOS init script issue and sleep
Tom Lane wrote:
Tony Caduto <[EMAIL PROTECTED]> writes:
I tried changing the sleep command in the script to 2, but at boot it
still says [FAILED].
even though the script reports it failed, the db is up an running.
This seems to happen for some people and not others. I've been wanting
to find out how the heck it can take multiple seconds for the postmaster
to start and create its pid-file ... that shouldn't take long at all.
Are you willing to try strace'ing the postmaster? Modify the script
like
$SU -l postgres -c "strace -tt -o /tmp/strace.out $PGENGINE/postmaster -p '$PGPORT' -D '$PGDATA' ${PGOPTS}
&" >> "$PGLOG" 2>&1 < /dev/null
^^ add this ^^
and reboot. (After you've gotten a trace of a failing case, change it
back and reboot again.)
This is kind of invasive and may change the behavior enough that we
don't see the problem :-( --- but if you're willing to reboot a few
times in hopes of capturing a trace of a failed case, it'd be worth
trying.
regards, tom lane
Hi Tom,
I added the strace line like you said and rebooted, it did display the
[FAILED] after the reboot.
I put the resulting strace.out file on my web server, here is the
link(warning it's petty big):
http://www.amsoftwaredesign.com/downloads/strace.out
After the second reboot I changed the sleep from 2 to 5 and then it
worked correctly, of course this really slowed the boot process.
Thanks,
Tony
---(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: [HACKERS] 8.04 and RedHat/CentOS init script issue and sleep
Hi all, I tried changing the sleep command in the script to 2, but at boot it still says [FAILED]. even though the script reports it failed, the db is up an running. System is a Compaq DL380(2.5gb ram 2.4 dual 2.4gzh Xeon) running CentOS 4.2 I am going to install 8.1beta 3 on another box that is the exact same hardware and OS version, I will report back what happens. Not sure what is going on, has anyone else had this problem with CentOS 4.2 or Red Had EL 4.2? Thanks, Tony Caduto http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql 8.x ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] 8.04 and RedHat/CentOS init script issue
Hi, I installed 8.04 via RPM on Centos 4.2 which is the same as RedHat 4.2 and while booting the init script reports that the daemon [FAILED], but after I logon it shows the postmaster running and I am able to connect from any client remotely. I made not modifcations to the script and there is nothing out of the ordinary in the log. Thanks, Tony ---(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: [HACKERS] More problems with the win32 installer for 8.1 beta3
Magnus Hagander wrote: Hi all, I sent out a message about this before, but for reasons beyond my control, I could not continue that thread. Anyway, not only does the installer blow away libpq.dll, it also removes all the Open SSL dlls, this is even more troubling because LOTS of other apps depend on OpenSSL. This morning when I got to work I downloaded beta3 and uninstalled 8.03, then installed it (beta3) and of course did not select PG Admin III, then did a search for libpq.dll and it was gone, not sure if uninstalling 8.03 got rid of it or if installing 8.1 did the deed. I then went ahead and used a remote control app that uses open SSL, then I get "Can't load libeay32.dll" So in conclusion either the installer or uninstaller is blowing away system DLLS without even asking me if I want to keep them, this is very bad behavior. The uninstall will indeed remove the files. The install will not touch them. OpenSSL libraries should never have gone in SYSTEM32, because they contain no versioning information. If they did, they could be dealt with in a better way in the installer. Right now we're more or less at the mercy of Windows Instlaler, which will remove the files that it originally installed unless someone else registered in the MSI database that they were using it. Your solution to this is to copy the openssl DLL files to each applications binary directory. It may suck, but that's how you'll have to do it :( I actually tried that with libpq and it didn't really work because of the libintl-2.dll and libiconv-2.dll dependencies. I removed libpq.dll,libintl-2.dll and libiconv-2.dll from the system32 dir, I placed all three in my applications directory and when I loaded libpq.dll it complained that it could not find libintl-2.dll and libiconv-2.dll, I can only conclude that paths to libintl-2.dll and libiconv-2.dll are hard coded in libpq.dll somewhere. Is there anyway to get a libpq that does not depend on libintl-2.dll and libiconv-2.dll? (short me spinning my wheels for hours trying to figure out C code). Thanks, Tony ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] More problems with the win32 installer for 8.1 beta3
Hi all, I sent out a message about this before, but for reasons beyond my control, I could not continue that thread. Anyway, not only does the installer blow away libpq.dll, it also removes all the Open SSL dlls, this is even more troubling because LOTS of other apps depend on OpenSSL. This morning when I got to work I downloaded beta3 and uninstalled 8.03, then installed it (beta3) and of course did not select PG Admin III, then did a search for libpq.dll and it was gone, not sure if uninstalling 8.03 got rid of it or if installing 8.1 did the deed. I then went ahead and used a remote control app that uses open SSL, then I get "Can't load libeay32.dll" So in conclusion either the installer or uninstaller is blowing away system DLLS without even asking me if I want to keep them, this is very bad behavior. Thanks, Tony ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Possible issue with win32 installer(8.1beta 3)...
Hi, I just installed the win32 8.1beta 3 installer on the same PC as my client, and I selected not to install PGAdmin III for obvious reasons and the installer seemed to remove my existing libpq.dll, I went to start up Delphi, and got tons of errors saying "could not load libpq.dll" did a search for libpq.dll and sure enough the installer blew it away. I am pretty sure it did not have this behavior before, but I could be wrong. The installer needs to have a option to install the client libs seperately if PGAdmin III is not selected for installation OR leave the currently installed libs alone OR prompt the user that it is going to replace them or remove them. (PG Admin III is not the only admin program around you know) I would normally just have a copy of libpq.dll in the same dir as my exe, but with the latest XP service packs this does not work anymore, not to mention libpq.dll seems to have several dependencies that also must be in system32 i.e. libpq.dll seems to have these dependencies hard coded to system32, because if I remove the dependencies from system32 and put them the same dir as my exe, I get libpq errors saying it can't find libiconv-2.dll libintl-2.dll even though they are in the same dir. Can't these two dependencies be staticly linked into libpq.dll to ease deployment issues? If libpq.dll was all self contained, I could rename it to something just my app could use and then I would not have to worry about the server setup blowing away libpq.dll. Who would care if it made the dll bigger? you have to deploy all the files anyway. (I don't know any thing about C, so I can't try myself) Thanks, -- Tony Caduto http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql 8.x ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Found small issue with OUT params
Martijn van Oosterhout wrote: On Fri, Sep 30, 2005 at 10:20:34AM -0500, Tony Caduto wrote: Tom, I hardly think the overhead would be significant on modern processors, I don't think the majority of users are running on Pentium 90s.( I am assuming you mean a performance overhead) Um, please read the documention. Returning a tuple is *significantly* more expensive than returning a single value. You have to get the tuple descriptor, allocate memory for the tuple, fill in all the fields with your data... For a single value you just return it. See here for all the details, you really don't want to do it if you don't need to. http://www.postgresql.org/docs/8.0/interactive/xfunc-c.html#AEN30497 Now, you could fudge the parser to automatically alter the name of the value in the function but I'm have no idea how hard that would be... So you might notice little performance hit bringing back a million rows, and most of these type of single OUT params functions only return one row/value anyway. There would be zero perceivable difference in performance regardless of the extra overhead for a single value/row. As a application developer, I don't care about tuples etc, I just want it to work as expected without having to resort to hacks like creating a second OUT param that is not used, otherwise I would have to change a lot of client code where ever the OUT param is refernced by name instead of position and that is done a lot because the position is more likely to change than the name. The bottom line(regardless of any overhead or if I read the docs about returning a tuple) is that if you have a OUT param it should return that name, not the name of the function, period. Thanks, Tony ---(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: [HACKERS] Found small issue with OUT params
Tom Lane wrote: Mike Rylander <[EMAIL PROTECTED]> writes: Using that logic, a functions with one OUT param would be the same as a function returning a rowtype with only one column, But it's not (and no, I don't want to make it so, because the overhead for the useless record result would be significant). regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend Tom, I hardly think the overhead would be significant on modern processors, I don't think the majority of users are running on Pentium 90s.( I am assuming you mean a performance overhead) The whole point is the current behavior is inconsistent and not expected and should be changed to be inline with the way other DB systems work. What is the point of even allowing a single OUT param then? You might as well just raise a error and tell the user that a single OUT param is not allowed. 8.1 is going to bring even more users over from systems like Firebird, MS SQL and even Oracle, and all of these allow a single OUT param and it returns the name of the OUT param, not the name of the function. Like I said before this behavior is going to make it more difficult to port applications from other systems. How difficult can it be to check if the function has a single OUT param as compared to the old way of using RETURN? Sorry if I am being a pain in the you know what, but I really think I am correct on this matter. Thanks, Tony ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Found small issue with OUT params
Tom Lane wrote: Tony Caduto <[EMAIL PROTECTED]> writes: Please don't take this the wrong way, but don't you think even if a single param is declared as OUT it should return the name of the OUT param? Not really, because "create function foo (in x int, out y float)" is supposed to have the same external behavior as "create function foo (in x int) returns float". I agree it's a bit of a judgment call, but I do not see a case for changing it. regards, tom lane Hi Tom, I understand where you are coming from, but I really think it should be changed because that is how every other DB I know of works with a single OUT param. I was recently porting a fairly large application from Firebird/Interbase and I had a bunch of functions that had one output param, and in the win32 application that I was also moving over, it was expecting the name of the OUT param, not the name of the function, So either I change every single instance of the client code to now use the function name or I add another dummy OUT param so my app does not have to be modified. The biggest reason to change this behavior is for porting from other Databases so client code does not need to be needlessly modifed. The new IN/OUT/INOUT params are sweet, and aside from this one issue, it made porting the Firebird procs super easy. I know I don't have much pull with development, but I think it should be changed for the 8.1 release. Thanks, Tony ---(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: [HACKERS] Found small issue with OUT params
Tom Lane wrote:
Tony Caduto <[EMAIL PROTECTED]> writes:
If I call it like this:
select * from FIND_USER_SOCKET_BYNAME('juser');
I would expect to get back 1 value with the name of the OUT param
(OUT_SOCKET_ADDRESS).
However it comes back with the name of the function
This is intentional, for compatibility with the pre-existing behavior
with functions in FROM. A function that isn't returning a record is
effectively declared as
FROM foo(...) AS foo(foo)
while a function that does return a record type gives you
FROM foo(...) AS foo(col1, col2)
regards, tom lane
Tom,
Please don't take this the wrong way, but don't you think even if a
single param is declared as OUT it should return the name of the OUT param?
If the function has no OUT params and uses the return keyword it should
return the name of the function, if it has one or many out params it
should return even a single column as the name of the OUT param.
It seems inconsistant that just because I have one OUT param declared it
does not return the name I specified for that param.
Isn't it possible to detect that the function has a single OUT param
declared and if a OUT param is declared return that name?
I am bringing this up because people coming over from Oracle or MS SQL
server will notice something like this.
Thanks,
Tony Caduto
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[HACKERS] Found small issue with OUT params
Hi,
consider this function:
CREATE OR REPLACE FUNCTION FIND_USER_SOCKET_BYNAME (
IN IN_USERNAME VARCHAR,
OUT OUT_SOCKET_ADDRESS INTEGER)
AS
$BODY$
BEGIN
select socket_address from userdata where fullname = in_username into
out_socket_address;
if out_socket_address is null then
out_socket_address = 0 ;
end if;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
If I call it like this:
select * from FIND_USER_SOCKET_BYNAME('juser');
I would expect to get back 1 value with the name of the OUT param
(OUT_SOCKET_ADDRESS).
However it comes back with the name of the function which I would expect
if I called it like this:
select FIND_USER_SOCKET_BYNAME('juser');
If I add another OUT value then the value comes back with the name of
the out param(plus the temp one I added) as expected.
It's easy enough to work around, but was not as expected.
Thanks,
Tony Caduto
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[HACKERS] 8.1 win32 beta?
With there be a win32 version of beta2? or a beta1? Thanks, Tony ---(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: [HACKERS] pg_restore bug on win32
Tom Lane wrote:
I wrote:
Hmm. The only relevant-looking change between 8.0.0 and 8.0.1 is
this one:
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/bin/pg_dump/pg_backup_archiver.c.diff?r1=1.101.4.2;r2=1.101.4.3;f=h
I wonder if this could be messing up the password acceptance --- for
instance, by causing CR or LF to not be stripped off what you type.
I've applied a patch that should fix it if that is the source of the
problem. I can't test it though, for lack of a Windows setup.
regards, tom lane
Index: pg_backup_archiver.c
===
RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_backup_archiver.c,v
retrieving revision 1.101.4.7
diff -c -r1.101.4.7 pg_backup_archiver.c
*** pg_backup_archiver.c17 May 2005 17:30:41 - 1.101.4.7
--- pg_backup_archiver.c11 Sep 2005 00:32:33 -
***
*** 1714,1724
/*
* On Windows, we need to use binary mode to read/write non-text archive
!* formats. Force stdin/stdout into binary mode in case that is what
* we are using.
*/
#ifdef WIN32
! if (fmt != archNull)
{
if (mode == archModeWrite)
setmode(fileno(stdout), O_BINARY);
--- 1714,1725
/*
* On Windows, we need to use binary mode to read/write non-text archive
!* formats. Force stdin/stdout into binary mode if that is what
* we are using.
*/
#ifdef WIN32
! if (fmt != archNull &&
! (AH->fSpec == NULL || strcmp(AH->fSpec, "") == 0))
{
if (mode == archModeWrite)
setmode(fileno(stdout), O_BINARY);
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
If someone can create a binary, I can test it in my environment.
Tony
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[HACKERS] pg_restore bug on win32
I have verfied this bug exists in all pg_restore versions greater than 8.0.0 Someone else reported it way back in Feb: http://groups.google.com/group/mailing.database.pgsql-bugs/browse_thread/thread/4dacdd43b894a2c3/e59e3203bb22745b?lnk=st&q=pg_restore+password+authentication+failed&rnum=9&hl=en#e59e3203bb22745b basicly pg_restore prompts for the password, but no matter what you enter it fails, the only way it works is to have the server pg_hba.conf file set to trust. version 8.0.0 works, so it was the result of some change in versions later than 8.0.0 Anyone know if this is resolved for 8.1? Thanks, Tony ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 8.1 and syntax checking at create time
When I run this I get this error in the database: PostgreSQL Error Code: (1) ERROR: function "plpgsql_validator" does not exist In an already-loaded database, I think the following should work: UPDATE pg_language SET lanvalidator = 'plpgsql_validator'::regproc WHERE lanname = 'plpgsql'; I'd recommend wrapping the update in a transaction and making sure only one record was updated before committing. Tom (or anybody else), are there any gotchas with updating pg_language like this? It works for me in simple tests. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] 8.1 and syntax checking at create time
Tom, I successfully updated my database to use the validator function without dropping it using: CREATE FUNCTION "plpgsql_validator" (oid) RETURNS void AS '$libdir/plpgsql' LANGUAGE C; UPDATE pg_language SET lanvalidator = 'plpgsql_validator'::regproc WHERE lanname = 'plpgsql'; The create checking is *much* better now :-) Thanks to everyone for helping me track this down, turned out it had nothing to do with 8.1 but I didn't know that. Sorry about that. Tony That would not create a dependency from the language to the validator, but in practice you probably don't care about that. The bigger problem for Tony is likely to be that plpgsql_validator() doesn't exist as a function in his database; he'll have to create it (see createlang -e for a reference) first. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.1 and syntax checking at create time
I just found out the databases on 8.0 where originally restored from a 7.4 server, so it seems I have never had the lanvalidator function even while running on 8.0 for the last 10 months :-( So how can I update my restored databases, i tried dropping the language, but it wouldn't let me becasuse of dependent objects. Thanks, Tony Are you using a database that was restored from an earlier version of PostgreSQL? I wonder if you're not getting the lanvalidator function. What's the result of the following query? SELECT lanname, lanplcallfoid, lanplcallfoid::regprocedure, lanvalidator, lanvalidator::regprocedure FROM pg_language; What happens if you create a fresh database and run "createlang plpgsql" in it, and then run your tests? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.1 and syntax checking at create time
Hi, I did restore from a 8.0 dump. here is the output from the query: lanname | lanplcallfoid | lanplcallfoid | lanvalidator | lanvalidator --+---++--+-- internal | 0 | - | 2246 | fmgr_internal_validator(oid) c| 0 | - | 2247 | fmgr_c_validator(oid) sql | 0 | - | 2248 | fmgr_sql_validator(oid) plperlu | 16392 | plperl_call_handler() |0 | - plpgsql | 16394 | plpgsql_call_handler() |0 | - (5 rows) here is my version string: PostgreSQL 8.1beta1 on i686-pc-linux-gnu, compiled by GCC i686-pc-linux-gnu-gcc (GCC) 3.3.5-20050130 (Gentoo Linux 3.3.5.20050130-r1, ssp-3.3.5.20050130-1, pie-8.7.7.1) I am trying my tests on a new database with fresh language install now. How can I get my restored databases to behave the same as a fresh one? Thanks for your help on this. Tony ---(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: [HACKERS] 8.1 and syntax checking at create time
Tom, What exactly does it check then? What I pointed out is simple "syntax" checking in other languages. From what I have seen it does not check anything in the body of the function, I can put gibberish in the body as long as it has a begin and end. It does not seem to be doing anything differently than 8.0.x does with function syntax checking at create time, so why even mention it in the release notes? the function below also raises no errors at create, but at run time it does. If I run the below function I get this error: PostgreSQL Error Code: (1) ERROR: type "record44" does not exist From what I read in the release notes I was expecting to see this raised at create time. Users coming from systems like Oracle and M$ SQL server are expecting this stuff to be caught at create not run time. How difficult would it be to have the server just run the function at create time with null for any input params? Of course a user could just do this but it is a annoying second step. CREATE or REPLACE FUNCTION public.test_func9(out firstname varchar,out lastname varchar) RETURNS SETOF pg_catalog.record AS $BODY$ Declare row record44; BEGIN asfdfdfdfafdsfsdfsdf sdf bla bla sdf yada yada s df sd fsd END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; Thanks, Tony Tom Lane wrote: Tony Caduto <[EMAIL PROTECTED]> writes: notice the for in select, it's for sure wrong, but it raises no errors until I execute the function also note the declaration for row, there is no record56 type, but it raises no errors at create. It's *syntax* checking, not an exhaustive check that the function is OK. 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: [HACKERS] 8.1 and syntax checking at create time
here is a case that does not work: CREATE or REPLACE FUNCTION public.test_func9(out firstname varchar,out lastname varchar) RETURNS SETOF pg_catalog.record AS $BODY$ Declare row record56; BEGIN for $0 in select '',description from common.common_groups loop -- firstname = row.description; -- lastname = ''; RETURN NEXT; end loop; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; notice the for in select, it's for sure wrong, but it raises no errors until I execute the function also note the declaration for row, there is no record56 type, but it raises no errors at create. here is my version string: PostgreSQL 8.1beta1 on i686-pc-linux-gnu, compiled by GCC i686-pc-linux-gnu-gcc (GCC) 3.3.5-20050130 (Gentoo Linux 3.3.5.20050130-r1, ssp-3.3.5.20050130-1, pie-8.7.7.1) Alvaro Herrera wrote: On Wed, Aug 31, 2005 at 11:39:48AM -0500, Tony Caduto wrote: I saw in the release notes that 8.1 is supposed to do function syntax checking at create rather than run time, but with the first beta this does not seem to work. check function bodies is on by default in the postgresql.conf file. Is there a setting that didn't make it into the conf file? It works for me; care to submit an test case? ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] 8.1 and syntax checking at create time
I saw in the release notes that 8.1 is supposed to do function syntax checking at create rather than run time, but with the first beta this does not seem to work. check function bodies is on by default in the postgresql.conf file. Is there a setting that didn't make it into the conf file? Thanks, Tony ---(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
[HACKERS] 8.1 OUT params returning more than one row question
Hi, I have been playing around with 8.1(it's very nice by the way) and was trying to get OUT params to return more than 1 row. I came up with the function below, and it does work, however I had to declare another record to use in the FOR ..IN loop. From my reading of the docs the out params create a record type automaticly and my question is how can I use this automaticly created record in the FOR loop? It does not seem right that I have to create another record type and then copy the row values to the out parms. CREATE OR REPLACE FUNCTION test_func9(out firstname varchar, out lastname varchar) RETURNS SETOF record AS $BODY$ Declare row record; BEGIN for row in select null,description from common.common_groups loop firstname = row.description; lastname = ''; RETURN NEXT; end loop; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; Thanks, Tony ---(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: [HACKERS] Delphi+pqsql
check out http://www.zeoslib.net I tried some of the commercial ones below and the postgresdac one had problems with schemas. It seems it does not fully support Postgres 7.3 and up. There really is a shortage of quality postgres TDataSet components for Delphi, Zeoslib is the best one, but it also incorporates drivers for like 6 other databases. It would be nice to see a nice open source set dedicated just to PostgreSQL. You might want to post on the Borland News groups as with the win32 version of Postgres becoming production ready very soon :-) you might get a lot of help with a Postgres Tdataset project. I was a Firebird SQL user for a long time, but I will never go back after using Postgres, it is just light years ahead. yes, comercial http://www.vitavoom.com/Products/ dbexpress driver, clasic ODBC or postgresdac http://www.sharewareconnection.com/postgresdac.htm Regards Pavel Stehule ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] Developing win32 admin tool for Postgresql 8 and have run into a problem
Hi, If this is not the appropriate list I apologize. Anyway, I am using Borland Delphi 7 and Zeoslib PG access components and I have just noticed that when I restore a database from 7.x and then load the function source into my program and make a change, i.e. compile it, the next time I open it the all the carriage return line feeds are gone, it's just one big string. The function continues to work, but is a pain to edit. I saved it in my app and then opened using PG admin and normally you can see the CRLFs in the property view, but they are totally gone. This did work fine when my test servers where on 7.4.x. I even converted all the CRLF to LF and that also failed. If I do a brand new function on 8.0 I don't have any problems, the CRLF stay. If anyone has any idea why this would be happening on 8.0 and not on 7.x please let me know, I know you are all busy with the 8.0 release so I will understand if no one replies. Thanks, Tony Caduto AM Software Design http://www.amsoftwaredesign.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
