On K, 2005-08-31 at 12:23 -0400, Tom Lane wrote:
> Hannu Krosing <[EMAIL PROTECTED]> writes:
> > My wild guess is that deleting all index pointers for a removed index is
> > more-or-less the same cost as creating new ones for inserted/updated
> > page.
>
> Only if you are willing to make the remov
Tom Lane <[EMAIL PROTECTED]> wrote:
> I think this is incredibly ugly :-(.
Yes, I think so, too :-(My patch is product of the thought that
I don't want to modify codes widely. So if we want to do it more cool way,
lots of changes are needed as you said.
> I'm also less than enthused about u
On Thu, 1 Sep 2005, Robert Treat wrote:
> running vanilla configure, i see the following toward the end, and my makefile
> is incomplete as well:
>
Old problem.
http://archives.postgresql.org/pgsql-ports/2003-04/msg00015.php.
>
> I couldnt seem to find a conftest.s1 file nor the sed command bein
Alvaro Herrera <[EMAIL PROTECTED]> wrote:
> Now, one thing of note is that you need to "compress" the page in order
> to actually be able to use the just-freed space. VACUUM could do that,
> but maybe it would be better to do it on-line -- the freezing process is
> going to have to write the page
running vanilla configure, i see the following toward the end, and my makefile
is incomplete as well:
checking alignment of int... (cached) 4
checking alignment of long... (cached) 4
checking alignment of long long int... (cached) 4
checking alignment of double... (cached) 4
checking for POSIX si
On 2005-09-01, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
> On Thu, Sep 01, 2005 at 01:57:02AM +0100, Simon Riggs wrote:
>
>> > If you're using autovacuum then the problem is already taken care of.
>>
>> autovacuum will respond only to UPDATEs and DELETEs. In the scenario I
>> outline, these will *
On 2005-09-01, Tom Lane <[EMAIL PROTECTED]> wrote:
> Jaime Casanova <[EMAIL PROTECTED]> writes:
>> I see this TODO item:
>> * %Set proper permissions on non-system schemas during db creation
>> I think a quetion here is wich are non-system schemas?
>
> There's considerable feeling that that TODO it
Tom,
> If you're using autovacuum then the problem is already taken care of.
> It will be taken care of better in 8.2, if we add per-table tracking
> of XID wraparound risk, but it's handled now. The only way that this
> proposal makes any sense is if you are trying not to vacuum at all, ever.
H
Gavin, Tom,
> Well, from my reading of some of the early papers, VACUUM was kind of
> different to what it is now. The idea was that expired data would be moved
> out the heap and stored else where. A timetravel mechanism could be used
> to see previous versions of the row.
And from talking to a
Alvaro,
> One thing that comes to mind is that this makes somewhat easier to build
> a tool to write pre-built tables, for bulk-loading purposes. You just
> construct the binary file with the HEAP_FROZEN bit set, and then attach
> the file to a dummy table. (Then again, you can do it today, usin
On Wed, 31 Aug 2005, Tom Lane wrote:
> Christopher Kings-Lynne <[EMAIL PROTECTED]> writes:
> >> I really really do not like proposals to introduce still another kind
> >> of VACUUM. We have too many already; any casual glance through the
> >> archives will show that most PG users don't have a gri
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes:
>> I really really do not like proposals to introduce still another kind
>> of VACUUM. We have too many already; any casual glance through the
>> archives will show that most PG users don't have a grip on when to use
>> VACUUM FULL vs VACUUM. Th
ITAGAKI Takahiro <[EMAIL PROTECTED]> writes:
> I think it would be a waste to retain xmin and cmin for frozen tuples
> because their values represent only 'visible for all transactions'.
True, but the hard part is getting rid of the storage for them.
> I wrote a makeshift patch to compress xmin a
I really really do not like proposals to introduce still another kind
of VACUUM. We have too many already; any casual glance through the
archives will show that most PG users don't have a grip on when to use
VACUUM FULL vs VACUUM. Throwing in some more types will make that
problem exponentially
William ZHANG wrote:
- Original Message -
From: "Andrew Dunstan" <[EMAIL PROTECTED]>
To: "Dave Page"
Cc: "William ZHANG" <[EMAIL PROTECTED]>;
Sent: Wednesday, August 31, 2005 10:24 PM
Subject: Re: [HACKERS] Call for 7.5 feature completion
Dave Page wrote:
* Compile with
Jaime Casanova <[EMAIL PROTECTED]> writes:
> I see this TODO item:
> * %Set proper permissions on non-system schemas during db creation
> I think a quetion here is wich are non-system schemas?
There's considerable feeling that that TODO item is bogus anyway.
It was pushed in by people who think th
On Thu, Sep 01, 2005 at 10:45:44AM +0900, ITAGAKI Takahiro wrote:
Hi,
> I think it would be a waste to retain xmin and cmin for frozen tuples
> because their values represent only 'visible for all transactions'.
> Additionally, most tuples in database can be frozen potentially.
I think this is a
Simon Riggs <[EMAIL PROTECTED]> writes:
> On Wed, 2005-08-31 at 19:24 -0400, Tom Lane wrote:
>> If you don't remove any tuples,
>> you don't scan the indexes anyway IIRC.
> No. Even if you remove *zero* tuples, an index is still scanned twice.
> Once to not delete the rows and once to not delete t
Simon Riggs <[EMAIL PROTECTED]> writes:
> On Wed, 2005-08-31 at 19:06 -0400, Tom Lane wrote:
>> In fact, it had better be able to, since once the CREATE INDEX commits,
>> pre-existing xacts are responsible to insert index entries for anything
>> they insert into the table.
> So would it be possibl
Hi Hackers,
I think it would be a waste to retain xmin and cmin for frozen tuples
because their values represent only 'visible for all transactions'.
Additionally, most tuples in database can be frozen potentially.
I wrote a makeshift patch to compress xmin and cmin (8bytes) to
1-bit flag, using
On Thu, Sep 01, 2005 at 01:57:02AM +0100, Simon Riggs wrote:
> > If you're using autovacuum then the problem is already taken care of.
>
> autovacuum will respond only to UPDATEs and DELETEs. In the scenario I
> outline, these will *never* occur on the largest tables. A VACUUM would
> still event
Hi,
I see this TODO item:
* %Set proper permissions on non-system schemas during db creation
I think a quetion here is wich are non-system schemas?
i guess "public" is one of these... but in pg_namespace i have no way
to know that. Is there another way? or maybe we need an attribute to
know tha
On Wed, 2005-08-31 at 19:24 -0400, Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > A new command is proposed - VACUUM MINIMAL. The *sole* purpose of this
> > command is to do the absolute minimum required to avoid transaction id
> > wraparound. (Better names welcome)
>
> I do not
On Wed, 2005-08-31 at 19:06 -0400, Tom Lane wrote:
> Andrew - Supernews <[EMAIL PROTECTED]> writes:
> > On 2005-08-31, Simon Riggs <[EMAIL PROTECTED]> wrote:
> >> During CREATE INDEX we include all tuples, even if they are already dead
> >> when we build an index.
> >>
> >> What purpose does this
Simon Riggs <[EMAIL PROTECTED]> writes:
> A new command is proposed - VACUUM MINIMAL. The *sole* purpose of this
> command is to do the absolute minimum required to avoid transaction id
> wraparound. (Better names welcome)
I do not see the point. If you only need to run it every billion
trans
Andrew - Supernews <[EMAIL PROTECTED]> writes:
> On 2005-08-31, Simon Riggs <[EMAIL PROTECTED]> wrote:
>> During CREATE INDEX we include all tuples, even if they are already dead
>> when we build an index.
>>
>> What purpose does this serve?
>>
>> A pre-existing transaction can't see the index,
On 2005-08-31, Simon Riggs <[EMAIL PROTECTED]> wrote:
> During CREATE INDEX we include all tuples, even if they are already dead
> when we build an index.
>
> What purpose does this serve?
>
> A pre-existing transaction can't see the index,
Yes, it can; the catalog is read in SnapshotNow rather th
For a while now, I've been seeking a method of reducing the impact of
VACUUM when run against a database where 80-95% of the data is
effectively read only and will not be deleted/updated again. This is the
situation in most Data Warehouses. When you get very large databases
(VLDB) the execution ti
I wrote:
> We've had repeated problems with PL languages stemming from the fact
> that pg_dump dumps them at a pretty low semantic level. Aside from this
> problem with adding a validator, we used to have issues with hardwired
> paths to the shared libraries in the CREATE FUNCTION commands. And i
During CREATE INDEX we include all tuples, even if they are already dead
when we build an index.
What purpose does this serve?
A pre-existing transaction can't see the index, so there is no danger
that it can use the index and unknowingly avoid touching a valid row.
(If it *can* see the index, is
huaxin zhang wrote:
I am new to this hacker's job. What I was looking for was to record
the actual disk IO performed for arbituary query plan. I searched
in backend/executor but not sure if that was the right place to
add a tracer. would the /backend/storage be the place that controls
the a
Martijn van Oosterhout writes:
> I was thinking actually of setting the type searching code to search
> pg_catalog before the normal search_path. The types being hardwired
> into the grammer essentially implied this so I thought I would avoid
> surprises.
That strikes me as an unnecessary reducti
On Wed, 2005-08-31 at 16:50 +0900, Satoshi Nagayasu wrote:
> Simon Riggs wrote:
> >>4. Allow to repair fragmentation in each page.
> >>
> >>Because updates cause fragmentation in the page.
> >>
> >>So we need to keep large continuous free space in each page,
> >>if we want to get more effective on
I am new to this hacker's job. What I was looking for was to record
the actual disk IO performed for arbituary query plan. I searched
in backend/executor but not sure if that was the right place to
add a tracer. would the /backend/storage be the place that controls
the actual I/O? btw, is t
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 r
Michael Fuhr <[EMAIL PROTECTED]> writes:
> In an already-loaded database, I think the following should work:
> UPDATE pg_language SET lanvalidator = 'plpgsql_validator'::regproc
> WHERE lanname = 'plpgsql';
> Tom (or anybody else), are there any gotchas with updating pg_language
> like this? It
[Please don't top-post; it destroys the conversational flow. I've
moved your comment below what you commented on.]
On Wed, Aug 31, 2005 at 03:13:02PM -0500, Tony Caduto wrote:
> >In an already-loaded database, I think the following should work:
> >
> >UPDATE pg_language SET lanvalidator = 'plpgsq
On Wed, Aug 31, 2005 at 02:25:54PM -0400, Tom Lane wrote:
> I still like the idea of pushing the aliasing out of the grammar,
> though. Come to think of it, we could probably even handle the
> multiple-word stuff that way: let the grammar convert CHARACTER VARYING
> to "character varying" and have
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 ch
Tony Caduto wrote:
Hi,
I did restore from a 8.0 dump.
[snip]
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?
Run "createlang plpgsql mydb" before running your restore, and possibly
remove
On Wed, Aug 31, 2005 at 11:59:47AM -0700, Josh Berkus wrote:
> There is a difference between *syntax* errors and *sql* errors.If a
> table does not exist, we don't want to check for that and bounce the
> function; possibly the function will only be called in a context where the
> table does
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 becasus
On Wed, Aug 31, 2005 at 07:43:45PM +, Matt Miller wrote:
> On Wed, 2005-08-31 at 15:29 -0400, Tom Lane wrote:
> > Matt Miller <[EMAIL PROTECTED]> writes:
> > > I don't remember the last time I intended to write code that referenced
> > > something that did not exist in the database.
> >
> > Al
On Wed, Aug 31, 2005 at 03:37:21PM -0400, Andrew Dunstan wrote:
> Tony Caduto wrote:
> >How can I get my restored databases to behave the same as a fresh one?
>
> Run "createlang plpgsql mydb" before running your restore, and possibly
> remove the bits that create them from the dump script, or th
On Wed, 2005-08-31 at 15:29 -0400, Tom Lane wrote:
> Matt Miller <[EMAIL PROTECTED]> writes:
> > I don't remember the last time I intended to write code that referenced
> > something that did not exist in the database.
>
> Almost every day, people try to write stuff like
>
> CREATE TEMP TAB
On Wed, 2005-08-31 at 11:59 -0700, Josh Berkus wrote:
> If a table does not exist, we don't want to check for that and bounce
> the function; possibly the function will only be called in a context
> where the table does exist.
The Pl/pgSQL compiler should be able to dive into SQL statements, hit
t
Michael Fuhr <[EMAIL PROTECTED]> writes:
> Are you using a database that was restored from an earlier version
> of PostgreSQL? I wonder if you're not getting the lanvalidator
> function.
Ah-hah, that sounds like a good theory. He'd have had to have carried
the DB forward from 7.4 or before, thou
Matt Miller <[EMAIL PROTECTED]> writes:
> On Wed, 2005-08-31 at 11:59 -0700, Josh Berkus wrote:
>> If a table does not exist, we don't want to check for that and bounce
>> the function; possibly the function will only be called in a context
>> where the table does exist.
> I am thankful that Oracl
Matt,
> On Wed, 2005-08-31 at 11:59 -0700, Josh Berkus wrote:
> > If a table does not exist, we don't want to check for that and bounce
> > the function; possibly the function will only be called in a context
> > where the table does exist.
>
> The Pl/pgSQL compiler should be able to dive into SQL
Hi,
I did restore from a 8.0 dump.
here is the output from the query:
lanname | lanplcallfoid | lanplcallfoid | lanvalidator |
lanvalidator
--+---++--+--
internal | 0 | -
Tony,
> 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.
Nope:
stp=# create function bad_stuff ( x boolean ) returns boolean as $x$
stp$# begin
stp$# afasdfasdfasdf;
stp$# afasdfasdfa;
stp$# asdf
On Wed, Aug 31, 2005 at 01:13:00PM -0500, Tony Caduto wrote:
> 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
Tony Caduto <[EMAIL PROTECTED]> writes:
> 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
>
Tony Caduto <[EMAIL PROTECTED]> writes:
> 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 checking is more extensive than it was in 8.0. For example 8.0
didn't reject this at cre
Martijn van Oosterhout writes:
> On Wed, Aug 31, 2005 at 11:11:04AM -0400, Tom Lane wrote:
>> One possible approach is to remove the aliasing translation from the
>> grammar altogether, and add a notion of "alias" entries in pg_type that
>> would be found through normal lookup and then replaced by
On Wed, 2005-08-31 at 13:13 -0500, Tony Caduto wrote:
> the function below also raises no errors at create, but at run time it does.
> ...
> CREATE or REPLACE FUNCTION public.test_func9(out firstname varchar,out
> lastname varchar)
> RETURNS SETOF pg_catalog.record AS
> $BODY$
> Declare
> row reco
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 diff
On Wed, Aug 31, 2005 at 11:11:04AM -0400, Tom Lane wrote:
> IMHO, ideally the aliasing should *only* apply to the built-in types.
> The current hack only approximates this (IIRC, the translation happens
> for any unqualified type name, independently of one's search path).
>
> One possible approach
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 tha
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.descrip
On Wed, Aug 31, 2005 at 11:27:39AM -0500, Tony Caduto wrote:
Hi,
> 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
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 f
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?
T
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
Hannu Krosing <[EMAIL PROTECTED]> writes:
> My wild guess is that deleting all index pointers for a removed index is
> more-or-less the same cost as creating new ones for inserted/updated
> page.
Only if you are willing to make the removal process recalculate the
index keys from looking at the del
On K, 2005-08-31 at 10:33 -0400, Tom Lane wrote:
> Hannu Krosing <[EMAIL PROTECTED]> writes:
> > On K, 2005-08-31 at 16:50 +0900, Satoshi Nagayasu wrote:
> >> VACUUM generates a huge load because it repaires all pages
> >> on the table file.
> >>
> >> I think (more light-weight) repairing on a sin
Martijn van Oosterhout writes:
> My question is, should users be able to create types schema.int4 and
> schema.integer simultaneously. Currently it allows you but it's not
> handled very well (\dT doesn't list both). Should this be allowed?
> Should aliasing for DEC and DECIMAL -> NUMERIC be done
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi,
On Tue, 30 Aug 2005, Ricardo Gamero wrote:
Hello everybody!
I'm so sorry to post this simple question but I don't know what to do, the
thing is I need to install postgresql 8.0.3 in red hat 9 but when I try to
do it this errors appear:
[EMAIL
Hannu Krosing <[EMAIL PROTECTED]> writes:
> On K, 2005-08-31 at 16:50 +0900, Satoshi Nagayasu wrote:
>> VACUUM generates a huge load because it repaires all pages
>> on the table file.
>>
>> I think (more light-weight) repairing on a single page
>> is needed to maintain free space in the specific
Dave Page wrote:
* Compile with MSVC on Win32 platforms. MySQL support it.
So what? It would take a major amount of work, with no useful benefits.
... and you can compile all the client and library stuff with MSVC -
just not the server nor extensions. But the audience for comp
On Wed, Aug 31, 2005 at 12:08:10PM -, prasanna mavinakuli wrote:
> We need to insert binary data to tables and retrieve the
> Same-(data type is bytea).
> We are using PQExecParams for inserting and retrieving
> Data.
This doesn't belong on pgsql-hackers, which is for discussing
development o
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of William ZHANG
> Sent: 31 August 2005 10:51
> To: pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] Call for 7.5 feature completion
>
> * Faster bulk load
Done, iirc.
> * Compile with MSVC on W
Hi,
As part of previous discussions about typmod for user type, Tom
mentioned that you would need to make type and function names
equivalent. As it turns out, if you refactor a few rules, you can
actually make it work and manage them seperately. For this to work the
current "col_name_keyword" non-
* Updatable Views per SQL
* INTERVAL data type per SQL
* BLOB/CLOB data type per SQL
* Faster bulk load
* Remove "current transaction is aborted, commands ignored ..."
* Compile with MSVC on Win32 platforms. MySQL support it.
* Thread safety libpq, ecpg.
--
Regards,
William ZHANG
--
Hi,
Problem Description:
We need to insert binary data to tables and retrieve the
Same-(data type is bytea).
We are using PQExecParams for inserting and retrieving
Data.
Table contains other than binary data also.
When we tried to fetch the integer data (type-int2)
We are not able to get
On 8/26/05, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
Or, slightly different, what are people's most wanted features?
One feature, or rather set of features which was missing from the list and I think
it is important: i18n. :)
I mean, PostgreSQL has a number of good features concerning internation
On K, 2005-08-31 at 16:50 +0900, Satoshi Nagayasu wrote:
> Simon Riggs wrote:
> >>4. Allow to repair fragmentation in each page.
> >>
> >>Because updates cause fragmentation in the page.
> >>
> >>So we need to keep large continuous free space in each page,
> >>if we want to get more effective on PC
On Wed, 31 Aug 2005 [EMAIL PROTECTED] wrote:
>
> With psql 8.1beta1 I get a pretty cryptic error message "ERROR: bogus varno:
> 1"
> occasionally when I try to mix postgis (GIST-based) conditions with normal
> conditions. The statements where this happen are totally reroducable.
Thanks for deta
Simon Riggs wrote:
>>4. Allow to repair fragmentation in each page.
>>
>>Because updates cause fragmentation in the page.
>>
>>So we need to keep large continuous free space in each page,
>>if we want to get more effective on PCTFREE feature.
>
>
> ...doesn't VACUUM already do that?
VACUUM gener
With psql 8.1beta1 I get a pretty cryptic error message "ERROR: bogus varno: 1"
occasionally when I try to mix postgis (GIST-based) conditions with normal
conditions. The statements where this happen are totally reroducable.
Attached is an example of this happening. I'd be happy to look furth
80 matches
Mail list logo
