Re : [pgadmin-support] Postgresql con DotNetNuke

Hola Luis,

Esto es una mailing lista en ingles solo !
Ademas, se trata solo del programo pgAdmin III aquí y no de PostgreSQL Server.
Tienes un problema de conexión a PostgreSQL y no es un problema de pgAdmin pero de configuración de PostgreSQL.

Probablemente que tienes que modificar el data/pg_hba.conf

Tendrás información en español al sitio:
http://www.postgresql.cl/

Y hay mailing lista en español tan bien:
pgsql-es-ayuda : http://archives.postgresql.org/pgsql-es-ayuda/


----- English ----

Hi Luis,

This is a mailing list in english only.
More over, this is only about the program pgAdmin and not the PostgreSQL Server.
....

 

Have fun,
L@u
The Computing Froggy

----- Message d'origine ----
De : Luis Nieto <lnto_27@yahoo.com>
À : pgadmin-support@postgresql.org
Envoyé le : Mercredi, 20 Août 2008, 15h08mn 27s
Objet : [pgadmin-support] Postgresql con DotNetNuke

saludos necesito ayuda ya que intento conectar DotNetNuke que es un manejador de contenidos con postgresql 8.1.3 todo bajo windows, visual studio express pero obtengo un error: No se ha podido establecer conexión ya que el equipo de destino ha denegado activamente dicha conexión que tipo de modificaciones puedo hacer para correguir ese error, es por .net o por el pgadmin III cualquier informacion que me pueda servir por favor pueden enviarme a mi correo de antemano gracias por su respuestas att.  luis

---

Yahoo! MTV Blog & Rock
¡Cuéntanos tu historia, inspira una canción y gánate un viaje a los Premios MTV!
Participa aquí http://mtvla.yahoo.com/

---

Envoyé avec Yahoo! Mail.
Une boite mail plus intelligente.

Re: [HACKERS] proposal sql: labeled function params

2008/8/20 Tom Lane <tgl@sss.pgh.pa.us>:
> "Pavel Stehule" <pavel.stehule@gmail.com> writes:
>> I understand now why Oracle use => symbol for named params. This isn't
>> used so operator - so implementation is trivial.
>
> You really didn't understand the objection at all, did you?
>
> The point is not about whether there is any built-in operator named =>.
> The point is that people might have created user-defined operators named
> that.

I understand well, so only I don't see better solution. Yes, everyone
who used => should have problems, but it is similar with .. new
keywords, etc. Probably easy best syntax doesn't exist :(. I haven't
idea who use => now and how often, and if this feature is possible in
pg, but there are not technical barriers.

regards
Pavel Stehule

>
> regards, tom lane
>

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Patch: plan invalidation vs stored procedures

On Wed, Aug 20, 2008 at 09:16:56AM -0400, Andrew Sullivan wrote:
> On Wed, Aug 20, 2008 at 03:12:43PM +0300, Asko Oja wrote:
>
> > - If there is nothing that can be done in 8.3 at least warning should be
> > added into the documentation. It will be just one more don't in our long
> > list don'ts for our developers.
>
> I am in favour of that change in the 8.3 branch.
+1

>
> >
> > ERROR: cache lookup failed for function.
> > - Could the plan be marked as invalid so it would fail only once so the next
> > call to the function would get replanned and work again. At least it would
> > be better than losing parts of application for indeterminate time.
>
> That seems to me to be a behaviour change, not a bug fix. I agree
> that the current behaviour is pretty annoying. That is not the same
> thing as "a bug" except in the loosest sense. The system works as
> specified, and therefore it's not a bug. If the specification is
> wrong, you need a new specification; that's a "bug fix" that is
> usually pronounced "major release".
>
> > - Could some less dangerous looking mechanism be added to 8.3 that wouldn't
> > make users not used to PostgreSQL limitations gasp for air when they see the
> > workarounds :)
>
> I think it a very bad idea even to suggest that we start undertaking
> things like adding mechanisms to minor releases, even with smileys at
> the end of the sentence. I appreciate (possibly more than many
> hackers) the limitations that are imposed on users by some of the
> decisions historically taken by developers in some of the previous
> major releases. But I very strongly agree with Dimitri: the
> super-conservative approach to maintenance releases that this project
> takes is a really big benefit to users, and is ultra important in
> "mission critical" environments. Otherwise, it becomes practically
> impossible to get minor releases into production. If you have to
> worry about the possibility of major changes between minor versions,
> you will have to treat every release as a major release.
>
+10

This policy has allowed us to upgrade to new minor releases with a
minimum of testing for critical systems and basically none for non-
critical systems. We would never upgrade for minor releases if this
changes. We do not have the resources to perform full regression
tests without having a very big carrot such as the new features a
major release contains.

Cheers,
Ken

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[GENERAL] Re: pg_restore fails on Windows

Magnus Hagander wrote
> Tom Tom wrote:
> > Magnus Hagander wrote
> >> Tom Lane wrote:
> >>> =?us-ascii?Q?Tom=20Tom?= <cobold@seznam.cz> writes:
> >>>> Magnus Hagander wrote:
> >>>>> Attached is a pg_restore.exe off CVS tip today, which should include the
> >>>>> patch. Please try this one.
> >>>> I tested the restore using the provided pg_restore.exe. The output is:
> >>>> pg_restore: [archiver (db)] could not execute query: could not send data
> to
> >> server: No buffer space available (0x00002747/10055)
> >>> According to
> >>> http://support.microsoft.com/kb/201213
> >>> this is an acknowledged bug that's been broken since Windows 95, so
> >>> I suppose we should conclude that M$ is unwilling or incompetent to
> >>> fix it.
> >> Yup, I was just reading that one when I saw your email. I finally got
> >> around to building a libpq with this change in it - attached here. Tom
> >> (not Lane), can you test this please?
> >>
> >> It shouldn't be this one really, since it doesn't list any modern
> >> Windows versions as having this issue, but it's worth a try.
> >
> > Tested. The restore comes through successfuly with the patched libpq.
> > So I take it that it's caused by the MS issue. Again, we are using WinXP
> Professional SP2. Perhaps the
> > system buffer space was _increased_ in XP (10MB comes through easily),
> > still if the block is too large, it occurs (speculation).
>
> Yes, that sounds quite likely. They fixed the symptoms, but not the
> underlying problem.
>
>
> > Since I don't know the implementation details of the patch I'd like to ask:
> > 1.This is not official patch, didn't pass the review/test cycle; do you think
> that it can be used in the
> > production environment (any side effects or so..)? If not, is the patch due
> for a next version?
>
> I plan to apply it to HEAD and supported back-branches (8.3 and 8.2) now
> that you have verified that it works, so it will be in the next
> versions. The only potential side-effect is that it will be slightly
> slower on packets >64kb, but I doubt that's even measurable in most cases.
>
> So yes, it should be safe to use in production.
>
>
> > 2.Our production PG version is 8.1.3. For some reasons it is not possible to
> upgrade to the LATEST;
> > I tested the libpq also on this version and it worked. Is it OK? I mean, did
> it worked by chance or the library
> > API & contracts didn't change between this version and latest?
>
> Note that libpq is only the *client* side. There is no patch necessary
> on the server. It might be easier to upgrade than the server?

This I didn't know/realize. It's good enough for us to use only the *client* side from the HEAD.
I tried the pg_restore from HEAD + patched libpq (on 8.1 installation) and it complained about missing zlib1 library. When
supplied, next was libintl3 dll. Further I didn't check. Obviously the library dependencies have changed since the 8.1.
How can I tell, which libraries/executables/resources of the installation are part of the *client* side (namely pg_restore),
so that I can use it independently from the server version?

>
> Did you test it with the pg_restore that I sent, or with the one from
> 8.1? The pg_restore I sent was for HEAD, as well as the libpq I sent, so
> you shouldn't use those in production long-term.
>
> For binaries, we don't provide backpatches for 8.1 any more (it's not a
> supported platform on Windows!), but you might be able to use the latest
> 8.2 libpq with the 8.1 pg_restore - you'll have to try that once the
> release is eventually out.
>
> Or you can just apply the patch to the latest 8.1 libpq and build it
> yourself, of course. I think it should apply just fine.
>

Tomas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Fwd: Restarting with pg_ctl, users, and passwords.

Matthew Pettis wrote:

> I've created a database and can log into it and do stuff with the
> tables using psql. However, I have a CGI app that wants to call the
> database (all on the same machine), but gets a 'FATAL: Ident
> authentication failed for user "postgres"' error.

You *REALLY* shouldn't use the `postgres' user for applications or
normal use. Create a new non-superuser, grant them the required
priveleges, and use them for the web application.

If you like you can make them the owner of a new database, so they can
do basically whatever they want with that database but not mess with
your others.

See the documentation for CREATE USER, CREATE ROLE, GRANT, and CREATE
DATABASE .

> now, while logged
> onto my Linux user account 'postgres', I can psql into my database
> without having to provide a password.

It's using ident authentication on a UNIX socket connection. The
database knows you are logged in on the UNIX account "postgres" (or have
su'd to it) because the operating system can tell it that. So it trusts you.

The web app will be using a TCP/IP connection to localhost, which (not
being a UNIX socket) cannot use unix socket IDENT auth. It can use ident
for passwordless auth anyway if you have an ident daemon running, but
traditionally you'd use a password instead.

For this to work, the server must be listening for TCP/IP connections
(see postgresql.conf) and be set to accept password auth for at least
the user and database you wish to log in to for 127.0.0.1/32 ("localhost").

> now, I *thought* what that would do would be to allow me to issue the
> 'psql' command from my regular non-postgres Linux account and log in
> as long as I would issue:
>
> psql -d mydb -U postgres -W
>
> and then provide 'postgres' as the password as well, as I had changed
> it in mydb as previously stated. But I am denied access when I try
> this from my account.

Because you're still using ident auth, as psql defaults to a UNIX
socket. It never uses your password because it already knows you're not
authorized according to pg_hba.conf's configuration for local UNIX sockets.

Use a TCP/IP connection instead by adding the argument
"--host localhost"
to the psql command line.

> I cannot even confirm that the database is running as I
> thought it would, since I don't know what process to look for in the
> 'ps -ef' dump.

"postgres"

In any case, if the DB wasn't running you'd get connection refused
errors or similar.

$ sudo /etc/init.d/postgresql-8.3 stop
[sudo] password for craig:
* Stopping PostgreSQL 8.3 database server
[ OK ]
$ psql
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket
"/var/run/postgresql/.s.PGSQL.5432"?
$ psql -h 127.0.0.1
psql: could not connect to server: Connection refused
Is the server running on host "127.0.0.1" and accepting
TCP/IP connections on port 5432?

--
Craig Ringer

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [HACKERS] Patch: plan invalidation vs stored procedures

On Wed, Aug 20, 2008 at 03:12:43PM +0300, Asko Oja wrote:

> - If there is nothing that can be done in 8.3 at least warning should be
> added into the documentation. It will be just one more don't in our long
> list don'ts for our developers.

I am in favour of that change in the 8.3 branch.

>
> ERROR: cache lookup failed for function.
> - Could the plan be marked as invalid so it would fail only once so the next
> call to the function would get replanned and work again. At least it would
> be better than losing parts of application for indeterminate time.

That seems to me to be a behaviour change, not a bug fix. I agree
that the current behaviour is pretty annoying. That is not the same
thing as "a bug" except in the loosest sense. The system works as
specified, and therefore it's not a bug. If the specification is
wrong, you need a new specification; that's a "bug fix" that is
usually pronounced "major release".

> - Could some less dangerous looking mechanism be added to 8.3 that wouldn't
> make users not used to PostgreSQL limitations gasp for air when they see the
> workarounds :)

I think it a very bad idea even to suggest that we start undertaking
things like adding mechanisms to minor releases, even with smileys at
the end of the sentence. I appreciate (possibly more than many
hackers) the limitations that are imposed on users by some of the
decisions historically taken by developers in some of the previous
major releases. But I very strongly agree with Dimitri: the
super-conservative approach to maintenance releases that this project
takes is a really big benefit to users, and is ultra important in
"mission critical" environments. Otherwise, it becomes practically
impossible to get minor releases into production. If you have to
worry about the possibility of major changes between minor versions,
you will have to treat every release as a major release.

I don't think we have sufficient commercial integration support yet
that we can follow the lead of the Linux kernel, where the system
vendor has the effective obligation to make sure your kernel actually
works.

In addition, if someone wants to develop back-patches for 8.3 that
give it new functionality otherwise planned for 8.4, I see nothing
wrong with them doing so. That's the advantage offered by having the
source. But the idea that the new functionality should be patched
back by the project because one is impatient is not on.

A

--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [BUGS] BUG #4340: SECURITY: Is SSL Doing Anything?

Magnus Hagander <magnus@hagander.net> writes:
> The code is there, actually, it's just #ifdef NOT_USED :-) From a *long*
> time ago, and the commit message just says "silence compiler warnings",
> so I've not managed to figure out why...

I think the commit you're looking for is this one:

2002-09-26 00:41 momjian

* doc/src/sgml/runtime.sgml, src/backend/libpq/be-secure.c,
src/interfaces/libpq/fe-secure.c: Allow SSL to work withouth
client-side certificate infrastructure.

so you'd probably need to root around in the archives from around then
to see why this was considered a good idea.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [ADMIN] Regarding access to a user

>>> Shashwat_Nigam <Shashwat_Nigam@satyam.com> wrote:

> Now what I want is to set privilege that whenever the user log in as
Us
> he can only see database Y, none other than that. The user Us could
have all
> rights for database Y but can't go for any other database (X or Z).

The first thing you need to do is configure "host based
authentication":

http://www.postgresql.org/docs/8.3/interactive/auth-pg-hba-conf.html

Among other things, this allows you to restrict which users can
connect to which databases.

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [GENERAL] SELECT query experts, anyone?

Teemu Juntunen wrote:
> Hi Experts,
>
> is threre any way to SELECT values in columns instead of rows? For
> example select products and their manufacters in the followin way:
>
> product1; manufacturer1; manufacturer2;,,, manufacturerN
> product2; manufacturer3;
> product3; manufacturer1;.. manufacturerN-1
>
> With a function you could put the product manufacturers in one string,
> but I would like to have them in columns.
>
> How about arrays. Is there a way to SELECT values in an array to columns?

This should work:

SELECT product,array(SELECT manufacturer FROM manufacturers WHERE
manufacturer_id=products.manufacturer_id) WHERE ...

or something along the lines of the above.

Regards
Tino

[pgadmin-support] Postgresql con DotNetNuke

saludos necesito ayuda ya que intento conectar DotNetNuke que es un manejador de contenidos con postgresql 8.1.3 todo bajo windows, visual studio express pero obtengo un error: No se ha podido establecer conexión ya que el equipo de destino ha denegado activamente dicha conexión que tipo de modificaciones puedo hacer para correguir ese error, es por .net o por el pgadmin III cualquier informacion que me pueda servir por favor pueden enviarme a mi correo de antemano gracias por su respuestas att.  luis

---

Yahoo! MTV Blog & Rock
¡Cuéntanos tu historia, inspira una canción y gánate un viaje a los Premios MTV!
Participa aquí http://mtvla.yahoo.com/

Re: [NOVICE] Restarting with pg_ctl, users, and passwords.

"Matthew Pettis" <matthew.pettis@gmail.com> writes:
> I've created a database and can log into it and do stuff with the
> tables using psql. However, I have a CGI app that wants to call the
> database (all on the same machine), but gets a 'FATAL: Ident
> authentication failed for user "postgres"' error. now, while logged
> onto my Linux user account 'postgres', I can psql into my database
> without having to provide a password.

If you want to switch to using password authentication, you'll need to
modify your postmaster's pg_hba.conf configuration file, which is
evidently currently set up for ident authentication. Read the "Client
Authentication" chapter of the manual ...

regards, tom lane

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

Re: [GENERAL] 8.3.3 win32 crashing

Tom Lane wrote:
> Klint Gore <kgore4@une.edu.au> writes:
> >>> [ heavy RAISE NOTICE traffic crashes the server ]
>
> > Tom Lane wrote:
> >> Please try to narrow it down a little.
>
> > client=warning, log=warning completes
> > client=warning, log=notice fails
> > client=notice, log=warning completes
> > client=notice, log=notice fails
> > It fails a heck of a lot quicker with client=warning, log=notice.
>
> Okay, so the problem is definitely on the postmaster-log side.
> Please show us all of your logging-related configuration settings.
>
> ISTR that Magnus was poking at some problem in the WIN32 log collector
> code, but whether this is related is not yet clear ...

Maybe it's related to another bug reported earlier. See here for the
complete references:
http://archives.postgresql.org/message-id/20080625133912.GB17573%40alvh.no-ip.org

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [HACKERS] A smaller default postgresql.conf

Alvaro Herrera wrote:
> Dave Page wrote:
>> On Wed, Aug 20, 2008 at 4:40 AM, Joshua Drake <jd@commandprompt.com> wrote:
>
>>> I am not arguing for this but if we went down that route it does buy us
>>> the ability to compartmentalize the entire conf.. so you have:
>>>
>>> memory_settings.conf
>>> logging.conf
>>> maintenance.conf
>> Would make it damn hard for pgAdmin to figure out how to edit the
>> config though. That's why we only support single-file configs atm.
>
> That's why we're working on adding a "sourcefile" attribute to GUC, so
> that it's trivial to know the path of the config file each settings
> comes from.

Still won't make life very easy for pgadmin, but it will make it
slightly less difficult.

That said, I think splitting the config out like that will mkae it even
more confusing for the newbie user, which is contrary to what we want here.

//Magnus

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Extending varlena

> Peter Eisentraut wrote:
>> If you replace the third point by "maybe partition TOAST tables", replace
>> large object handle by TOAST pointer, and create an API to work on TOAST
>> pointers, how are the two so much different? And why should they be?

The reason they should be different is that (IMHO anyway) you don't want
the default behavior of SELECT * FROM ... to include pulling back the
entire contents of the blob. Indeed, we *can't* have that be the
behavior, unless we want to go back to the proposal that started this
thread of making the entire system safe for multi-gigabyte datums.

It's certainly possible that the underlying implementation could be
just TOAST, but we need some other API at the SQL level.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [INTERFACES] schema name in the SQL statement.

Masis, Alexander (US SSA) wrote:
> My db has one schema. In fact I deleted the default "public" and created
> my own: "schema_1".
> In my SQL query I have to explicitly specify schema name:
> "select my_colomn from schema_1.table_name;"
> Can't schema be specified as default value during connection, or can I
> set the schema before I send the query?

You can change the database so that everybody connecting to it will use
that schema:
ALTER DATABASE your_db SET search_path = 'schema_1';

You can do that per-user, too:
ALTER DATABASE username SET search_path = 'schema_1';

You can also change it in the application, by sending
SET search_path TO 'schema_1';

There are other options too, but that should suffice.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--
Sent via pgsql-interfaces mailing list (pgsql-interfaces@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-interfaces

Re: [GENERAL] WAL archiving to network drive

On Tue, 19 Aug 2008, Rob Adams wrote:

> Is it best to give the 'postgres' user network access & archive the WAL files
> directly to the network drive? Or archive the WAL files to a local folder and
> then use a scheduled task to move them to the network drive?

I normally write first to a local drive then move to the network, because
that has some useful properties:

1) As as long as there's local disk space, the archive_command called
always returns quickly. While performance here doesn't really matter it
makes me feel better.

2) If there is a problem with the network drive, the slow/blocked network
write is not happening where the database is waiting for it.

3) In an emergency where the usual network drive is unavailable, you can
temporarily change your archive strategy without touching the database
server's configuration.

4) The copying process doesn't need read access to the main database, and
the database server process doesn't need permissions on the network drive.
I find myself giving it a completely different set of permissions, so that
it can't touch the database at all really, but can run alert scripts and
take actions I'd never want the database user to have access to.

The main downside of doing it this is way is you're stuck writing and
debugging a lot more scripts to make sure everything happens correctly.
I've found the alternative--just putting something into archive_command
does the transfer directly--isn't quite flexible enough when things go
wrong for me to be comfortable with it.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [pgsql-es-ayuda] empezar serial desde 1000

Carlos Mendez escribió:
> Hola Cesar, gracias por la respuesta,
>
> creo que con un ejemplo lo entendere mejor, si tengo la siguiente tabla,
> como lo defino para que *el campo id empieze en 1000?*
>
> CREATE TABLE personas (
> id serial NOT NULL, //este campo tiene que empezar en 1000 y no en 1
> nombre character varying(50) NOT NULL,
> PRIMARY KEY (id)
> )without oids;
>
> porque hice lo me dijeron pero el campo id sigue empezando en 1 y yo
> quiero que el campo id empieze en 1000,
>

CREATE TABLE personas (
id serial NOT NULL, //este campo tiene que empezar en 1000 y no en 1
nombre character varying(50) NOT NULL,
PRIMARY KEY (id)
)without oids;

ALTER SEQUENCE personas_id_seq RESTART WITH 1000;

--
TIP 10: no uses HTML en tu pregunta, seguro que quien responda no podrá leerlo

Re: [PERFORM] pgsql do not handle NULL constants in the view

Sergey Hripchenko <shripchenko@intermedia.net> writes:
> CREATE OR REPLACE VIEW bar AS
> SELECT *
> FROM (
> (
> SELECT calldate, duration, billsec, get_asterisk_cdr_caller_id(accountcode) AS caller_id
> FROM asterisk_cdr
> ) UNION ALL (
> SELECT start_time, get_interval_seconds(completed_time-start_time), get_interval_seconds(answered_time-start_time), NULL
> FROM asterisk_huntgroups_calls
> )
> ) AS foo;

Try casting the NULL to integer (or whatever the datatype of the other
union arm is) explicitly.

regards, tom lane

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [HACKERS] A smaller default postgresql.conf

Dave Page wrote:
> On Wed, Aug 20, 2008 at 4:40 AM, Joshua Drake <jd@commandprompt.com> wrote:

> > I am not arguing for this but if we went down that route it does buy us
> > the ability to compartmentalize the entire conf.. so you have:
> >
> > memory_settings.conf
> > logging.conf
> > maintenance.conf
>
> Would make it damn hard for pgAdmin to figure out how to edit the
> config though. That's why we only support single-file configs atm.

That's why we're working on adding a "sourcefile" attribute to GUC, so
that it's trivial to know the path of the config file each settings
comes from.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[GENERAL] SELECT query experts, anyone?

Hi Experts,

 

is threre any way to SELECT values in columns instead of rows? For example select products and their manufacters in the followin way:

 

product1; manufacturer1; manufacturer2;,,, manufacturerN

product2; manufacturer3;

product3; manufacturer1;.. manufacturerN-1

 

With a function you could put the product manufacturers in one string, but I would like to have them in columns. 

 

How about arrays. Is there a way to SELECT values in an array to columns?

 

Best regards and thanks,

Teemu Juntunen

Re: [HACKERS] proposal sql: labeled function params

"Pavel Stehule" <pavel.stehule@gmail.com> writes:
> I understand now why Oracle use => symbol for named params. This isn't
> used so operator - so implementation is trivial.

You really didn't understand the objection at all, did you?

The point is not about whether there is any built-in operator named =>.
The point is that people might have created user-defined operators named
that.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Is mdextend really safe?

Gregory Stark <stark@enterprisedb.com> writes:
> Now this only matters if we ever call mdextend on a block which isn't the
> block immediately following the end of file. Is that true?

Only in hash indexes.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [pgadmin-support] Bug in v1.8.4 - AutoCompletion vs running query

On Fri, Aug 15, 2008 at 4:38 PM, Horvath Gabor <dueyduey@freemail.hu> wrote:
> If I use autocompletion while a query started from the same edit
> window is already running, the whole application freezes until the
> query is finished, and a message appears in the message page reading:
> ********** Error **********
> The query gets committed, its results remain.

There are other potential issues here - for example, error markers can
end up in the wrong place.

For now, I've disabled editing of the text altogether whilst a query
is running. I can't think of any particularly compelling use cases for
allowing that, but I'm open arguments.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

--
Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support

Re: [BUGS] BUG #4340: SECURITY: Is SSL Doing Anything?

Tom Lane wrote:
> Magnus Hagander <magnus@hagander.net> writes:
>> I'd set the default to "verifypeer" in 8.4 and up, but backpatch it with
>> a default of "off". That way we don't break existing setups, but give
>> users the ability to verify if if they want to.
>
> This seems a bit large/invasive/new-feature-ish for a backpatch.

I agree in principle, but I really didn't want to leave our "old" users
in the cold, unable to actually use SSL to secure their systems..

The code is there, actually, it's just #ifdef NOT_USED :-) From a *long*
time ago, and the commit message just says "silence compiler warnings",
so I've not managed to figure out why...

//Magnus

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [pgsql-es-ayuda] [Off-topic] Jornadas Regionales Soft LIbre

Como quisiera tener el conocimiento debido sobre Postgres para poder
organizar y celebrar con uds alguna charla o jornada en mi país
Paraguay.
Lugar de dar la charla tengo, ya que soy alumno de la facultad
Politécnica de la Universidad Nacional de Asunción y está a disposición
el aula magna y varios laboratorios. Solo falta el conocimiento :-)

Exitos en Bs As!

El mar, 19-08-2008 a las 16:52 -0300, postgres Emanuel CALVO FRANCO
escribió:
> Buenas Tardes,
>
> Es solo para informales que en Bs As dentro de unos dias se celebraran
> las Jornadas Regionales de Soft Libre en las cuales el dia Jueves 21 a
> las 10,00 hs en el Aula Magna de la universidad de Belgrano se dara ua
> charla de Postgresql. Estan todos invitados a aquellos que puedan
> venir.
> --
> TIP 8: explain analyze es tu amigo
>
--
Teófilo Oviedo
Dpto. Técnico
ACTIVE S.R.L.
http://www.active.com.py

--
TIP 8: explain analyze es tu amigo

Re: [BUGS] BUG #4340: SECURITY: Is SSL Doing Anything?

Magnus Hagander <magnus@hagander.net> writes:
> I'd set the default to "verifypeer" in 8.4 and up, but backpatch it with
> a default of "off". That way we don't break existing setups, but give
> users the ability to verify if if they want to.

This seems a bit large/invasive/new-feature-ish for a backpatch.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[pgadmin-hackers] SVN Commit by dpage: r7406 - in trunk/pgadmin3: . pgadmin/frm

Author: dpage

Date: 2008-08-20 13:30:49 +0100 (Wed, 20 Aug 2008)

New Revision: 7406

Revision summary: http://svn.pgadmin.org/cgi-bin/viewcvs.cgi/?rev=7406&view=rev

Log:
Prevent the editor in the query tool being updated while a query is running.

Modified:
trunk/pgadmin3/CHANGELOG
trunk/pgadmin3/pgadmin/frm/frmQuery.cpp

--
Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers

[PERFORM] PostgreSQL+Hibernate Performance

Hi,

Can anyone suggest the performance tips for PostgreSQL using Hibernate.

One of the queries:

- PostgreSQL has INDEX concept and Hibernate also has Column INDEXes. Which is better among them? or creating either of them is enough? or need to create both of them?

and any more performace aspects ?

Thanks in advance.

==
KP

Re: [HACKERS] Patch: plan invalidation vs stored procedures

The lack of plan invalidation is limitation that also has two bugs attached to it.
I agree that full fledged patch to fix all the isssues should not be done in 8.3.
I can't agree that effort to get the bugs fixed already in 8.3 should not be made.
I can understand that hackers here have learned to live with these bugs and limitations but not all the users are reading these flame wars here and most of them are not even aware of these bugs until they are hit by them.

Sql function bug is such that users probably won't even understand what hit them and how the data got mangled.
- If there is nothing that can be done in 8.3 at least warning should be added into the documentation.  It will be just one more don't in our long list don'ts for our developers.

ERROR:  cache lookup failed for function.
- Could the plan be marked as invalid so it would fail only once so the next call to the function would get replanned and work again. At least it would be better than losing parts of application for indeterminate time.
- Should update pg_proc set proname = proname; be the current solution to the problem or has someone something better to offer. We could scan released code for DROP FUNCTION and generate plan invalidation statement as last item of transaction releasing the code.
- Could some less dangerous looking mechanism be added to 8.3 that wouldn't make users not used to PostgreSQL limitations gasp for air when they see the workarounds :)
Calling the problem limitation will not make it go away. I am quite sure that new users consider it a bug until thay are converted to perceive it as lmitation.

No matter how many time the usage of functions in database is called corner case it does not make it a corner case. In my experience it is quite common practice on all the database systems i have worked with. I do get the impression that Tom who would prefer to get all the pl's out of PostgreSQL and live happily ever after with pure SQL standard.

On Wed, Aug 20, 2008 at 11:27 AM, Dimitri Fontaine <dfontaine@hi-media.com> wrote:

Le mercredi 20 août 2008, Tom Lane a écrit :

> That just begs the question of what's the difference between a "bug" and
> a "limitation".  AFAICS, having such a policy/guideline/whatchacallit
> in place wouldn't have done a single thing to stop the current flamewar,
> because the people who want this thing back-patched are insisting that
> it's a bug, while those who don't are saying it's a long-known
> limitation.

As a person who previously insisted it was a bug, I'd like to take the
opportunity to claim that I didn't realize this was a limitation of the
design of plan invalidation, which now seems related to DDL operations.
Realizing this earlier would have resulted in no mail at all on this thread
from here.

There's certainly a balance between -hackers readers not doing their homework
and people in the know choosing not to re-estate known things...

> Also, there are a whole lot more considerations in a backpatch decision
> than just "is it a bug".  The (estimated) risk of creating new bugs and
> the extent to which the patch will change behavior that apps might be
> relying on are two big reasons why we might choose not to back-patch
> a bug fix.

And this way the project works is what leads its users not to fear minor
upgrades, which is something I (we all?) highly value.

Regards,
--
dim

Re: [BUGS] BUG #4340: SECURITY: Is SSL Doing Anything?

Peter Eisentraut wrote:
> Dan Kaminsky wrote:
>>>> 1) No roots (but still works for some unknown reason)
>>>> 2) Explicitly configured corporate roots
>>>> 3) Explicitly configured corporate roots, AND global roots
>>>> 4) Global roots (but still works for some unknown reason)
>
>> So, if you do nothing special, it's #1? Sounds like the path of least
>> resistance is no security. Uh oh.
>
> Yeah, in the average, if not common case, a user interested in SSL use would
> probably just follow the recipe in the documentation for creating and
> installing a self-signed certificate with no certificate checking in the
> client. Which, as you correctly observe, is pretty much completely useless.
>
> Someone should probably redesign, reconfigure, and redocument this.

Agreed.

I'd like to suggest that for the "easy fix" (without supporting custom
callbacks and whatever) we create a new connection parameter called
"sslverifypeer". It can be set to "verifypeer", "verifycert" or "off".
When set to "verifypeer", we will verify the peer name and the
certificate. When "verifycert" we just verify the certificate, fail if
we can't find a root certificate, but ignore the common name. "off"
should be self-explaining.

I'd set the default to "verifypeer" in 8.4 and up, but backpatch it with
a default of "off". That way we don't break existing setups, but give
users the ability to verify if if they want to.

Comments?

//Magnus

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[COMMITTERS] pgsql: Make libpq on windows not try to send chunks larger than 64Kb.

Log Message:
-----------
Make libpq on windows not try to send chunks larger than 64Kb.

Per Microsoft knowledge base article Q201213, early versions of
Windows fail when we do this. Later versions of Windows appear
to have a higher limit than 64Kb, but do still fail on large
sends, so we unconditionally limit it for all versions.

Patch from Tom Lane.

Tags:
----
REL8_3_STABLE

Modified Files:
--------------
pgsql/src/interfaces/libpq:
fe-misc.c (r1.133 -> r1.133.2.1)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/libpq/fe-misc.c?r1=1.133&r2=1.133.2.1)

--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers

[COMMITTERS] pgsql: Make libpq on windows not try to send chunks larger than 64Kb.

Log Message:
-----------
Make libpq on windows not try to send chunks larger than 64Kb.

Per Microsoft knowledge base article Q201213, early versions of
Windows fail when we do this. Later versions of Windows appear
to have a higher limit than 64Kb, but do still fail on large
sends, so we unconditionally limit it for all versions.

Patch from Tom Lane.

Tags:
----
REL8_2_STABLE

Modified Files:
--------------
pgsql/src/interfaces/libpq:
fe-misc.c (r1.130 -> r1.130.2.1)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/libpq/fe-misc.c?r1=1.130&r2=1.130.2.1)

--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers

[COMMITTERS] pgsql: Make libpq on windows not try to send chunks larger than 64Kb.

Log Message:
-----------
Make libpq on windows not try to send chunks larger than 64Kb.

Per Microsoft knowledge base article Q201213, early versions of
Windows fail when we do this. Later versions of Windows appear
to have a higher limit than 64Kb, but do still fail on large
sends, so we unconditionally limit it for all versions.

Patch from Tom Lane.

Modified Files:
--------------
pgsql/src/interfaces/libpq:
fe-misc.c (r1.134 -> r1.135)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/libpq/fe-misc.c?r1=1.134&r2=1.135)

--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers

Re: [BUGS] BUG #4340: SECURITY: Is SSL Doing Anything?

Dan Kaminsky wrote:
> >> 1) No roots (but still works for some unknown reason)
> >> 2) Explicitly configured corporate roots
> >> 3) Explicitly configured corporate roots, AND global roots
> >> 4) Global roots (but still works for some unknown reason)

> So, if you do nothing special, it's #1?  Sounds like the path of least
> resistance is no security.  Uh oh.

Yeah, in the average, if not common case, a user interested in SSL use would
probably just follow the recipe in the documentation for creating and
installing a self-signed certificate with no certificate checking in the
client. Which, as you correctly observe, is pretty much completely useless.

Someone should probably redesign, reconfigure, and redocument this.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [HACKERS] Is mdextend really safe?

Gregory Stark wrote:
> Now this only matters if we ever call mdextend on a block which isn't the
> block immediately following the end of file. Is that true?

I don't think so.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Is mdextend really safe?

Gregory Stark napsal(a):

> On Unix that creates a sparse file where the intervening blocks are not
> allocated. When we later write out those blocks the filesystem then has to
> allocate space for them. IIRC the bug reports were from Windows. I'm not sure
> what NTFS's behaviour with sparse files is.

NTFS has sparse file feature, but how it works ...

> Now this only matters if we ever call mdextend on a block which isn't the
> block immediately following the end of file. Is that true?

I think, that it could happens only during wal log replay, but at the
end everything should be OK. Look into ReadBuffer_common there is
following code:

00226 /* Substitute proper block number if caller asked for P_NEW */
00227 if (isExtend)
00228 blockNum = smgrnblocks(smgr, forkNum);

Zdenek

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Is mdextend really safe?

* Gregory Stark:

> On Unix that creates a sparse file where the intervening blocks are
> not allocated. When we later write out those blocks the filesystem
> then has to allocate space for them.

This seems to happen relatively rarely. Creating temporary holes like
this usually results in heavily fragmented files on the file systems I
use, and I don't see this with PostgreSQL. (It's one of my gripes
with Berkeley DB.)

However, I looked at the code recently and couldn't figure out *why*
PostgreSQL's observed behavior is this way. 8-(

--
Florian Weimer <fweimer@bfk.de>
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] proposal sql: labeled function params

Hello

I understand now why Oracle use => symbol for named params. This isn't
used so operator - so implementation is trivial.

postgres=# create function x(a boolean) returns bool as $$select $1$$
language sql;
CREATE FUNCTION
Time: 5,549 ms
postgres=# select x(a => true);
x
---
t
(1 row)

Time: 0,566 ms
postgres=# select x(a => 0 >= 1);
x
---
f
(1 row)

Time: 0,772 ms
postgres=# select x(a => 0 <= 1);
x
---
t
(1 row)

Time: 0,633 ms
postgres=# select x(a => 0 <= 1);

it could live together with labels
postgres=# select x(a => 0 <= 1 as boo);
x
---
t
(1 row)

there are not any conflict. nice (operator => is never used).

I dislike to use AS for named params - it has some unhappy consequences:
a) it merge two features together (named params, labels),
b) when we disable @a, then we should implement only one feature - named params
c) @b isn't compatible with SQL/XML that is implemented now

I don't found any notice about db2 default parameters.

Named params needs different algorithm of searching in pg_proc. There
should be some new problems - like

create function foo(a integer, b integer);
select foo(10,10); -- ok
select foo(a => 10, b =>20); -- ok
select foo(b=>20, a =>20); -- ok
select foo(c=>20, 20); -- unknown fce !!!

Regards
Pavel Stehule

real gram implemenation:
param_list: param
{
$$ = list_make1($1);
}
| param_list ',' param
{
$$ = lappend($1, $3);
}
;

param:
a_expr
{
$$ = $1;
}
| param_name POINTER a_expr
{
$$ = $3;
}
| a_expr AS ColLabel
{
$$ = $1;
}
| param_name POINTER a_expr AS ColLabel
{
$$ = $3;
}
;

lexer
identifier {ident_start}{ident_cont}*

typecast "::"
pointer "=>"

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [pgsql-fr-generale] [Rappel] Envoyez vos propositions de conférences pour le PGDay !

On Monday 18 August 2008 16:35:04 damien clochard wrote:
> Bonjour,
>
> Il vous reste deux semaines pour proposer une conférence !
>
>
> ====== Appel à conférences ======
>
>
> Dans la lignée des évènements PGDay ( "journée PostgreSQL" ) organisée en
> Italie, en Grande Bretagne, aux États-Unis , l'association des utilisateurs
> francophones de PostgreSQL
> ( PostgreSQLFr ), en partenariat avec Toulibre, lance une journée dédiée à
> PostgreSQL.
>
> Cette journée se tiendra le 4 octobre 2008.
>
> Les conférences se tiendront de 9h à 18h au centre culturel Soupetard à
> Toulouse.
>

Bonjour,

Je me réponds à moi-même pour préciser que nous organiserons également des
conférences-éclairs ("lightning talks") en fin de journée. Le principe est
assez simple : il s'agit de conférences plus courtes ( 10 à 15 minutes ) et
plus informelles ( il n'est pas obligatoire de préparer des slides )

Ces conférences sont souvent très appréciées car elles sont denses
et "rythmées", ce qui apporte une respiration par rapport aux conférences
plus longues et plus exhaustives.

Donc si vous manquez de temps pour préparer une conférence de 45 minutes ou si
vous ne pouvez pas divulguer trop de détails pour des raisons de
confidentialité, le format 10-15 minutes vous conviendra mieux.

Bref n'hésitez pas à nous soumettre vos propositions de lightning talks !

* si vous avez besoin d'aide sur un de vos projets Open-Source,
* si vous voulez présenter rapidement votre utilisation de PostgreSQL,
* si vous avez découvert un petit outil qui a changé votre quotidien de DBA,
* si vous avez des idées de patchs à réaliser
* etc....

Ces mini-conférences sont faites pour vous... :-)

Envoyez vos propostions ou vos questions à : bureau@postgresqlfr.org

Merci d'avance

--
damien clochard
http://dalibo.org | http://dalibo.com

--
Sent via pgsql-fr-generale mailing list (pgsql-fr-generale@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-fr-generale

[pgadmin-hackers] SVN Commit by dpage: r7405 - in trunk/pgadmin3/pgadmin: gqb include/gqb

Author: dpage

Date: 2008-08-20 11:24:18 +0100 (Wed, 20 Aug 2008)

New Revision: 7405

Revision summary: http://svn.pgadmin.org/cgi-bin/viewcvs.cgi/?rev=7405&view=rev

Log:
Further GQB cleanup. Also, ensure that the column selection popup uses the correct icon for views.

Modified:
trunk/pgadmin3/pgadmin/gqb/gqbBrowser.cpp
trunk/pgadmin3/pgadmin/gqb/gqbColumn.cpp
trunk/pgadmin3/pgadmin/gqb/gqbDatabase.cpp
trunk/pgadmin3/pgadmin/gqb/gqbQueryObjs.cpp
trunk/pgadmin3/pgadmin/gqb/gqbSchema.cpp
trunk/pgadmin3/pgadmin/gqb/gqbTable.cpp
trunk/pgadmin3/pgadmin/gqb/gqbView.cpp
trunk/pgadmin3/pgadmin/gqb/gqbViewPanels.cpp
trunk/pgadmin3/pgadmin/include/gqb/gqbArrayCollection.h
trunk/pgadmin3/pgadmin/include/gqb/gqbBrowser.h
trunk/pgadmin3/pgadmin/include/gqb/gqbCollection.h
trunk/pgadmin3/pgadmin/include/gqb/gqbModel.h
trunk/pgadmin3/pgadmin/include/gqb/gqbObject.h
trunk/pgadmin3/pgadmin/include/gqb/gqbViewPanels.h

--
Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers

PgWiki entry Shared DB Hosting (was Re: [ADMIN] Best practice running a shared DB hosting server)

> BTW, I'd encourage you to start a page on the wiki site to track the specific
> issues you run into, and specific fixes your using. Would certainly be
> helpful to others and probably yourself as a reference on the topic.

I've started a page as you suggested which at the moment contains only
some introductory mumblings and some details on my current approach,
comments and suggestions would be very welcome.

==>

http://wiki.postgresql.org/wiki/Shared_Database_Hosting

[GENERAL] Sequences using libpqxx

Hi all,

 

   Does the sequence commands like nextval().setval(),curval()  exist using libpqxx?

 

Thanks and regards

Roshni

Re: [GENERAL] pg_restore fails on Windows

Tom Tom wrote:
> Magnus Hagander wrote
>> Tom Lane wrote:
>>> =?us-ascii?Q?Tom=20Tom?= <cobold@seznam.cz> writes:
>>>> Magnus Hagander wrote:
>>>>> Attached is a pg_restore.exe off CVS tip today, which should include the
>>>>> patch. Please try this one.
>>>> I tested the restore using the provided pg_restore.exe. The output is:
>>>> pg_restore: [archiver (db)] could not execute query: could not send data to
>> server: No buffer space available (0x00002747/10055)
>>> According to
>>> http://support.microsoft.com/kb/201213
>>> this is an acknowledged bug that's been broken since Windows 95, so
>>> I suppose we should conclude that M$ is unwilling or incompetent to
>>> fix it.
>> Yup, I was just reading that one when I saw your email. I finally got
>> around to building a libpq with this change in it - attached here. Tom
>> (not Lane), can you test this please?
>>
>> It shouldn't be this one really, since it doesn't list any modern
>> Windows versions as having this issue, but it's worth a try.
>
> Tested. The restore comes through successfuly with the patched libpq.
> So I take it that it's caused by the MS issue. Again, we are using WinXP Professional SP2. Perhaps the
> system buffer space was _increased_ in XP (10MB comes through easily),
> still if the block is too large, it occurs (speculation).

Yes, that sounds quite likely. They fixed the symptoms, but not the
underlying problem.

> Since I don't know the implementation details of the patch I'd like to ask:
> 1.This is not official patch, didn't pass the review/test cycle; do you think that it can be used in the
> production environment (any side effects or so..)? If not, is the patch due for a next version?

I plan to apply it to HEAD and supported back-branches (8.3 and 8.2) now
that you have verified that it works, so it will be in the next
versions. The only potential side-effect is that it will be slightly
slower on packets >64kb, but I doubt that's even measurable in most cases.

So yes, it should be safe to use in production.

> 2.Our production PG version is 8.1.3. For some reasons it is not possible to upgrade to the LATEST;
> I tested the libpq also on this version and it worked. Is it OK? I mean, did it worked by chance or the library
> API & contracts didn't change between this version and latest?

Note that libpq is only the *client* side. There is no patch necessary
on the server. It might be easier to upgrade than the server?

Did you test it with the pg_restore that I sent, or with the one from
8.1? The pg_restore I sent was for HEAD, as well as the libpq I sent, so
you shouldn't use those in production long-term.

For binaries, we don't provide backpatches for 8.1 any more (it's not a
supported platform on Windows!), but you might be able to use the latest
8.2 libpq with the 8.1 pg_restore - you'll have to try that once the
release is eventually out.

Or you can just apply the patch to the latest 8.1 libpq and build it
yourself, of course. I think it should apply just fine.

//Magnus

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[HACKERS] Is mdextend really safe?

Earlier we saw some bug reports from someone who had a buffer flush fail do to
ENOSPC. We asserted then that that should never happen because when we extend
the relation we write out the new blocks so any ENOSPC errors out to happen at
that point, not when a buffer is flushed.

However looking at mdextend it only writes out the requested block. Any blocks
between the end of the table and the requested block are *not* written out. We
count on the OS to implicitly fill those blocks with zeros.

On Unix that creates a sparse file where the intervening blocks are not
allocated. When we later write out those blocks the filesystem then has to
allocate space for them. IIRC the bug reports were from Windows. I'm not sure
what NTFS's behaviour with sparse files is.

Now this only matters if we ever call mdextend on a block which isn't the
block immediately following the end of file. Is that true?

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Proposed Resource Manager Changes

On Wed, 2008-08-20 at 10:46 +0900, ITAGAKI Takahiro wrote:

> One thing to worry about is a confliction of RmgrId. We can check
> conflictions in redo because rmgrs are actually registered, but
> we might need to check conflictions even in a normal running.
> Extensions that write own XLog record can use arbitrary RmgrIds
> without restrictions.

That sounds quite hard. I'm putting in a check on the id itself, which
will be more than we had before...

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Patch: plan invalidation vs stored procedures

On Tue, 2008-08-19 at 19:45 -0400, Tom Lane wrote:
> Hannu Krosing <hannu@2ndQuadrant.com> writes:
> > If there is plan invalidation then you just change called1() to return
> > one more field and that's it - no juggling with C) and D) and generally
> > less things that can go wrong.
>
> That is a pure flight of fancy.

Nope, this is description of real situation when you have to maintain
lots and lots of functions.

> Adjusting a function's API generally
> requires source-code changes on the caller side too.

Adding a column to table does not (even generally) require changing all
queries accessing that table, why should adding a column to functions
return type do ?

> There might be
> a few limited cases where you can avoid that, but that doesn't leave
> you with much of an argument that this is a critical bug fix. It's
> a corner case and little more.

It is a corner case if you don't have a dynamic system, evolving over
time, which relies heavily on functions .

It is a complete non-issue if you don't use functions at all.

> FWIW, given that there will probably always be corner cases. I can see
> the attraction in Simon's suggestion of providing a way to manually
> issue a system-wide forced plan flush.

That was also what I suggested as one blanket way of solving the bigger
issue you brought up, that of not knowing where to stop tracking
dependencies for plan invalidation.

My thinking was, that this trades one-time inefficiency (replanning all
stored plans) against more general but spread in time inefficiency of
current patch (sending registration messages around for each function
OID you depend on at each time you plan ).

------------
Hannu

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [PERFORM] pgsql do not handle NULL constants in the view

Forgot to add

 

asteriskpilot=> SELECT version();

                                                version

--------------------------------------------------------------------------------------------------------

 PostgreSQL 8.2.9 on i386-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070925 (Red Hat 4.1.2-27)

(1 row)

 

asteriskpilot=> \q

[root@ast-sql data]# uname -a

Linux ast-sql.intermedia.net 2.6.23.1-21.fc7 #1 SMP Thu Nov 1 21:09:24 EDT 2007 i686 i686 i386 GNU/Linux

 [root@ast-sql data]# cat /etc/redhat-release

Fedora release 7 (Moonshine)

[root@ast-sql data]# rpm -qa | grep postgres

postgresql-8.2.9-1.fc7

postgresql-libs-8.2.9-1.fc7

postgresql-server-8.2.9-1.fc7

postgresql-contrib-8.2.9-1.fc7

postgresql-devel-8.2.9-1.fc7

 

---

From: Sergey Hripchenko
Sent: Wednesday, August 20, 2008 1:17 PM
To: 'pgsql-performance@postgresql.org'
Subject: pgsql do not handle NULL constants in the view

 

Hi all,

 

Looks like I found a bug with views optimization:

 

For example create a test view:

 

CREATE OR REPLACE VIEW bar AS

SELECT *

FROM (

    (

        SELECT calldate, duration, billsec, get_asterisk_cdr_caller_id(accountcode) AS caller_id

        FROM asterisk_cdr

    ) UNION ALL (

        SELECT start_time, get_interval_seconds(completed_time-start_time), get_interval_seconds(answered_time-start_time), NULL

        FROM asterisk_huntgroups_calls

    )

) AS foo;

 

And perform select on it:

 

EXPLAIN SELECT * FROM bar WHERE caller_id = 1007;

 

Theoretically second UNION statement shouldn’t be executed at all (because 1007 != NULL)… but postgres performs seq-scans on both UNION parts.

 

asteriskpilot=> EXPLAIN ANALYZE SELECT * FROM bar WHERE caller_id = 1007;

                                                                      QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------

 Subquery Scan foo  (cost=0.00..94509.49 rows=7303 width=28) (actual time=12249.473..14841.648 rows=25 loops=1)

   Filter: (caller_id = 1007)

   ->  Append  (cost=0.00..76252.26 rows=1460578 width=24) (actual time=0.065..13681.814 rows=1460405 loops=1)

         ->  Subquery Scan "*SELECT* 1"  (cost=0.00..57301.22 rows=1120410 width=20) (actual time=0.064..10427.353 rows=1120237 loops=1)

               ->  Seq Scan on asterisk_cdr  (cost=0.00..46097.12 rows=1120410 width=20) (actual time=0.059..8326.974 rows=1120237 loops=1)

         ->  Subquery Scan "*SELECT* 2"  (cost=0.00..18951.04 rows=340168 width=24) (actual time=0.034..1382.653 rows=340168 loops=1)

               ->  Seq Scan on asterisk_huntgroups_calls  (cost=0.00..15549.36 rows=340168 width=24) (actual time=0.031..863.529 rows=340168 loops=1)

 Total runtime: 14841.739 ms

(8 rows)

 

 

But if we wrap this NULL value into the _IMMUTABLE RETURNS NULL ON NULL INPUT_ function postgres handle this view properly

 

asteriskpilot=> EXPLAIN SELECT * FROM bar WHERE caller_id = 1007;

                                             QUERY PLAN

-----------------------------------------------------------------------------------------------------

 Append  (cost=20.21..15663.02 rows=1015 width=24)

   ->  Subquery Scan "*SELECT* 1"  (cost=20.21..3515.32 rows=1014 width=20)

         ->  Bitmap Heap Scan on asterisk_cdr  (cost=20.21..3505.18 rows=1014 width=20)

               Recheck Cond: (get_asterisk_cdr_caller_id(accountcode) = 1007)

               ->  Bitmap Index Scan on asterisk_cdr_caller_id  (cost=0.00..19.96 rows=1014 width=0)

                     Index Cond: (get_asterisk_cdr_caller_id(accountcode) = 1007)

   ->  Result  (cost=0.00..12147.69 rows=1 width=24)

         One-Time Filter: NULL::boolean

         ->  Seq Scan on asterisk_huntgroups_calls  (cost=0.00..12147.68 rows=1 width=24)

 

 

 

 

---

This message (including attachments) is private and confidential. If you have received this message in error, please notify us and remove it from your system.

[PERFORM] pgsql do not handle NULL constants in the view

Hi all,

 

Looks like I found a bug with views optimization:

 

For example create a test view:

 

CREATE OR REPLACE VIEW bar AS

SELECT *

FROM (

    (

        SELECT calldate, duration, billsec, get_asterisk_cdr_caller_id(accountcode) AS caller_id

        FROM asterisk_cdr

    ) UNION ALL (

        SELECT start_time, get_interval_seconds(completed_time-start_time), get_interval_seconds(answered_time-start_time), NULL

        FROM asterisk_huntgroups_calls

    )

) AS foo;

 

And perform select on it:

 

EXPLAIN SELECT * FROM bar WHERE caller_id = 1007;

 

Theoretically second UNION statement shouldn’t be executed at all (because 1007 != NULL)… but postgres performs seq-scans on both UNION parts.

 

asteriskpilot=> EXPLAIN ANALYZE SELECT * FROM bar WHERE caller_id = 1007;

                                                                      QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------

 Subquery Scan foo  (cost=0.00..94509.49 rows=7303 width=28) (actual time=12249.473..14841.648 rows=25 loops=1)

   Filter: (caller_id = 1007)

   ->  Append  (cost=0.00..76252.26 rows=1460578 width=24) (actual time=0.065..13681.814 rows=1460405 loops=1)

         ->  Subquery Scan "*SELECT* 1"  (cost=0.00..57301.22 rows=1120410 width=20) (actual time=0.064..10427.353 rows=1120237 loops=1)

               ->  Seq Scan on asterisk_cdr  (cost=0.00..46097.12 rows=1120410 width=20) (actual time=0.059..8326.974 rows=1120237 loops=1)

         ->  Subquery Scan "*SELECT* 2"  (cost=0.00..18951.04 rows=340168 width=24) (actual time=0.034..1382.653 rows=340168 loops=1)

               ->  Seq Scan on asterisk_huntgroups_calls  (cost=0.00..15549.36 rows=340168 width=24) (actual time=0.031..863.529 rows=340168 loops=1)

 Total runtime: 14841.739 ms

(8 rows)

 

 

But if we wrap this NULL value into the _IMMUTABLE RETURNS NULL ON NULL INPUT_ function postgres handle this view properly

 

asteriskpilot=> EXPLAIN SELECT * FROM bar WHERE caller_id = 1007;

                                             QUERY PLAN

-----------------------------------------------------------------------------------------------------

 Append  (cost=20.21..15663.02 rows=1015 width=24)

   ->  Subquery Scan "*SELECT* 1"  (cost=20.21..3515.32 rows=1014 width=20)

         ->  Bitmap Heap Scan on asterisk_cdr  (cost=20.21..3505.18 rows=1014 width=20)

               Recheck Cond: (get_asterisk_cdr_caller_id(accountcode) = 1007)

               ->  Bitmap Index Scan on asterisk_cdr_caller_id  (cost=0.00..19.96 rows=1014 width=0)

                     Index Cond: (get_asterisk_cdr_caller_id(accountcode) = 1007)

   ->  Result  (cost=0.00..12147.69 rows=1 width=24)

         One-Time Filter: NULL::boolean

         ->  Seq Scan on asterisk_huntgroups_calls  (cost=0.00..12147.68 rows=1 width=24)

 

 

 

 

---

This message (including attachments) is private and confidential. If you have received this message in error, please notify us and remove it from your system.