(select COALESCE(MAX(bid), 0) from bid where auction_id = a.auction_id)
as max_bid
regards, tom lane
e the only person who's very
unhappy ;-). I don't want to see us hold up TOAST that long, even if
it means not having some of the other features originally planned for
7.1...
regards, tom lane
ables, holding the row's creation timestamp.
An on-update trigger seems like a very simple solution here.
You could either copy the old value into the new, or raise an
error if they are different, depending on what you want.
regards, tom lane
hing rows in tableY for a tableX row. (DISTINCT might help if so.)
regards, tom lane
pect that I cannot insert a record into another table in a trigger.
You certainly can --- I have apps that do it all the time. I suspect
maybe the plpgsql parser gets confused by SQL-style comments? Your
example seems to work fine if I leave out the comment.
regards, tom lane
Alex Guryanow <[EMAIL PROTECTED]> writes:
> Is it possible to use INTERSECT and UNION keywords in subqueries?
No, not at the moment. This is one of many things we hope to fix when
we redesign querytrees (currently planned for 7.2 cycle).
regards, tom lane
N IN_COMMENT;
}
regards, tom lane
nless it's NULL, in which case
value2".
regards, tom lane
t long ago, and I forget what it looked like before. Perhaps
7.0.* is broken in this respect? Would think people would have noticed,
though.
regards, tom lane
be
null whether you want it to be or not. This has been gone over *many*
times before on this mail list, so I didn't think I needed to repeat it.
This will be fixed in 7.1 (is already fixed in current sources).
regards, tom lane
s later
when we redo querytrees, but the real limitation so far has been bogus
assumptions in the function-call API, not querytrees.
regards, tom lane
rrent time?
Use now():
CREATE TABLE test (x int, modtime timestamp default now() );
regards, tom lane
his does work in current sources and will be in 7.1.
I believe you could make this work in 7.0 by using an SQL function
instead of plpgsql, if that helps any...
regards, tom lane
x27;t.
Are you thinking about plpgsql's caching of query plans (and
specifically the table OIDs stored in those plans) or is there another
issue here?
We do need to think about invalidating cached query plans when updates
happen...
regards, tom lane
> unescaped versions of, eg \w, \s, \n and so on a pg seems to ignore them.
The regexp package we currently use implements POSIX 1003.2 regexps
(see src/backend/regex/re_format.7). I believe there is an item on the
TODO list about upgrading the regexp parser to something more modern
... feel free to hop on that project if it's bugging you ...
regards, tom lane
y premature constant folding --- see
nearby discussion of how to define a column default that gives the
time of insertion. You need to write this as
NEW.ChangedAt := now();
to prevent the system from reducing timestamp('now') to a constant
when the function is first executed.
regards, tom lane
life of a postmaster, so unless you're
constantly restarting the postmaster I don't see how we could be leaking
shmem.
However, rather than speculate, let's get some hard facts. Try using
"ipcs -m -a" to keep track of shared mem allocations, and see what usage
is creeping up over time.
regards, tom lane
ss you're restarting the postmaster on a regular basis that
doesn't seem like it'd be the issue. Anyway, keep an eye on things with
ipcs and we'll see what's going on...
regards, tom lane
he log file? That really shouldn't have been much of
a problem, AFAICS.
regards, tom lane
you need to keep track of multiple changes
intra-transaction). It might not be defined quite the way you want,
but if you're not picky about what a "version number" is, it'll do.
regards, tom lane
work in views right now. Hopefully by 7.2 there
will be a better way to do the view.
regards, tom lane
d)=sum(rf.cantidad)"
> and I get this result:
> ERROR: rewrite: comparision of 2 aggregate columns not supported
I think this is fixed in 7.0.
regards, tom lane
ndex keys in the data you're inserting?
I've recently been swatting some performance problems in the btree
index code for the case of large numbers of equal keys.
regards, tom lane
ces only one varchar column is
mistakenly counting the hidden IdSort column that's needed to sort by.
I don't know of any good workaround in 7.0, short of patching the
erroneous code. Have you thought about using a view, rather than a
function returning set?
regards, tom lane
ons comparable to the ones for IS NULL (in fact, IS UNKNOWN
should be equivalent to IS NULL except for requiring a boolean
input, AFAICT).
regards, tom lane
then the result is order-
dependent. You don't want that.
regards, tom lane
ll come back case-insensitive and
> oblivious to punctuation.
That's pretty bizarre (not to say difficult to believe). What LOCALE
setting are you running the postmaster in?
regards, tom lane
It also seems possible that no sort is happening at all (which would be
a planner bug), and the ordering you're getting is just whatever happens
to be in the underlying table. Does EXPLAIN show that the query is
being done with an explicit sort?
regards, tom lane
use the cast
method in reverse:
regression=# select 968518585 :: int4 :: abstime :: timestamp;
?column?
2000-09-09 12:56:25-04
(1 row)
(there's probably a cleaner way to do this, but that works ...)
regards, tom lane
;
foo
-
1
(1 row)
regression=# select foo(1);
foo
-
2
(1 row)
If you are coding at the C level you may need to call
CommandCounterIncrement() between queries.
regards, tom lane
ollowing foreign keys.
Actually, as the 7.1 code currently stands, a query that uses explicit
JOIN operators like yours does will always be implemented in exactly
the given join order, with no searching. I haven't quite decided if
that's a bug or a feature ...
regards, tom lane
ile "pg_geqo" in the
$PGDATA directory. (There should be a prototype file "pg_geqo.sample"
there for you to copy and edit.)
Peter Eisentraut has cleaned up the option handling for 7.1 so that GEQO
options are handled like all the others...
regards, tom lane
ut what to do next.
> I'm not sure which version of standards allows to bracket joins,
> but I know sybase accepts the above form.
SQL92 says
::=
|
|
so anything that claims to accept SQL92 had better allow parentheses
around JOIN expressions...
regards, tom lane
Meszaros Attila <[EMAIL PROTECTED]> writes:
> Can I test this feature in the current snapshot?
Sure. But see my message to pghackers on Tuesday for notes about what's
not working yet in the JOIN support.
regards, tom lane
;
> Use an explicit ordering operator or modify the query
> Is it a bug ?
No, I don't think so. The system has no way to intuit what datatype
you consider '2000-08-22' to be.
SELECT DISTINCT table_2.f1, table_1.f2, '2000-08-22'::date ...
would work.
regards, tom lane
Keith Wong <[EMAIL PROTECTED]> writes:
> Anybody know how to compile pgaccess from postgres source files?
I think it's driven by configure --with-tcl.
regards, tom lane
ation and similar issues"
in pghackers on 11-Sep.
regards, tom lane
email and more time coding...
regards, tom lane
"Michael Richards" <[EMAIL PROTECTED]> writes:
> It appears that postgres 7.0 does not support repeatable read for
> transaction isolation. Is this planned? If so, when?
?? Maybe you need to do SET TRANSACTION ISOLATION LEVEL?
regards, tom lane
Louis-David Mitterrand <[EMAIL PROTECTED]> writes:
> Aren't ORDER BY clauses allowed in subselects?
No. This is per SQL92...
regards, tom lane
the relevant table
declarations, so I can try the example without a lot of guessing?
regards, tom lane
Louis-David Mitterrand <[EMAIL PROTECTED]> writes:
> On Wed, Sep 20, 2000 at 10:43:59AM -0400, Tom Lane wrote:
>> Louis-David Mitterrand <[EMAIL PROTECTED]> writes:
>>>> How should I interpret that error?
>>>> ERROR: replace_vars_with_subp
G_INT64(0);
int64val2 = PG_GETARG_INT64(1);
PG_RETURN_INT64(val1 + val2);
}
which actually does about the same things under the hood, but you
don't have to sully your hands with 'em ...
regards, tom lane
. it will read the entire
>> table only to give me the first 10 while in release 6.5 it will fetch the
>> index for the first 10 in a very fast manner, indeed the 6.5 release
>> resolves in 1 second while the 7.0 release resolves in 10-20 sec.
> Hmm, I believe Tom Lane was doi
where '09/23/2000' between start_date and end_date;
I don't see what the effective_date table is buying ...
regards, tom lane
s 3 15:42:09
> (1 row)
Looks like a bug to me ... Thomas, do you agree?
Curiously, it appears that the interval input converter will accept this
with or without the 'days' keyword included.
regards, tom lane
ree with the part of the proposal that says
to return NULL instead of 'Not a view' when there is no view by the
given name, but I do not agree with trying to suppress errors due to
metadata problems.
regards, tom lane
.
I'm up to my armpits in subselect-in-FROM right now, but will put this
on my to-do list. Will look at it in a week or two if no one else has
fixed it before then...
regards, tom lane
Meszaros Attila <[EMAIL PROTECTED]> writes:
> How far is the above subject from beeing implemented?
I'm looking at it right now ... no promises yet, though.
regards, tom lane
:date::timestamp;
?column?
2000-10-29 01:00:00-04
(1 row)
Not sure how this would apply to 2000-01-01, though. What timezone
are you in, anyway?
regards, tom lane
don't understand why -O2
breaks spinlocks --- maybe egcs is misoptimizing around the inline
assembly code of tas() ?
regards, tom lane
trip yourself up when
moving across DBMSes.
regards, tom lane
where f2.parent_foo_id = table_foo.foo_id);
?
regards, tom lane
have to worry about
saving/reloading OIDs).
2. counter overflow problems hit you only per-table, not
per-installation.
regards, tom lane
to
> simulate it with a union all/subquery. For that reason I thought'd I'd ask
> here first to see if there is a better way.
Update to 7.0.* and do
select app_code, count(distinct state_code) from tab group by app_code;
regards, tom lane
ID sequence
generator over just as many tables as you need to have unique IDs
across. That way you don't have a problem at dump/reload time,
and you don't exhaust your ID space any faster than you must.
regards, tom lane
our installation has just one active
table, per-table sequence values look like a better bet.
BTW, there *is* talk of providing an 8-byte-OID option, but I'm not
holding my breath for it.
regards, tom lane
way off.
The plan is clearly handicapped by the lack of indexes on article.id
and articles_groups.groupid, also. You seem to have indexes on all
the wrong columns of articles :-( ... each of those indexes costs you
on updates, but will it ever be useful in a query?
regards, tom lane
ourtype to text. In general any type
coercion can be provided this way --- a function of a single argument,
having the same name as its return type, represents a coercion path.
regards, tom lane
lity"? That's the kind
of term that can mean different things to different people...
regards, tom lane
rant rights to yourself again :-(.
You don't need superuser help to do this, you just have to do
GRANT ALL ON table TO yourself
as the table owner. But it's stupid to have to do that when it's
supposed to be the default condition. Fixed for 7.1.
regards, tom lane
n a big hurry to make
that happen...
regards, tom lane
e table's access permissions whether
or not he's currently got any permissions granted to himself;
ie, changing permissions is not a grantable/revokable right,
it's just checked on the basis of who you are.
regards, tom lane
will most likely have to
do another initdb before beta, so loading lots and lots of data
into a snapshot installation is probably a waste of time.)
regards, tom lane
t on my todo list.
regards, tom lane
em.
Entry SQL is a pretty impoverished subset (no VARCHAR type, to take
a random example), so nearly everyone implements at least some
intermediate- and full-SQL features. But exactly which ones is
highly variable.
regards, tom lane
e (i.e. PHP4)?
Good question --- the interface code might or might not have a sensible
default behavior for types it doesn't recognize.
regards, tom lane
ng and recreating the index should help.
regards, tom lane
the site toplevel...
regards, tom lane
tuple elements and do things with them, *without*
re-evaluating the function for each such use. So I think what we really
want to do is to allow functions returning tuple sets to be elements
of a FROM clause:
select f1 + 1, f2 from find_tab(33);
This has been speculated about but no one's looked at what it would take
to make it work.
regards, tom lane
talk to each other...
We are implementing SQL around here, not Perl, so we have to follow
the SQL spec's definition of substr().
regards, tom lane
fault values, but they're all
there.
For an UPDATE, you could check to see whether old.col = new.col.
This would miss the case where an UPDATE command is explicitly setting
a column to the same value it already had; dunno if you care or not.
regards, tom lane
y
and with a different port number (-D and -P switches) and you're set.
Unless you run out of shared memory or some such, in which case some
tweaking of kernel parameters is called for...
regards, tom lane
, not char(n)). This example works OK in current sources,
but until 7.1 comes out you'll need to write something like
where login.login = lower('foo')::char;
Or change the login field to type text...
regards, tom lane
atch up inner and outer rows, which'd work a lot
better when there are large numbers of rows involved. It might actually
happen for 7.2...
regards, tom lane
Jie Liang <[EMAIL PROTECTED]> writes:
> su-2.04# make
> "../../../src/Makefile.global", line 135: Need an operator
> "../../../src/Makefile.global", line 139: Missing dependency operator
Hmm, is "make" on your machine GNU make? If not try "gmake".
regards, tom lane
#x27;s, in which alternate-DB environment variables are expanded.
(initlocation is an exception because it doesn't contact the postmaster)
regards, tom lane
It's got nothing at all to do
with starting additional postmasters.
regards, tom lane
essfully-matched rows.
Further down the pike, we have plans to make the system smart enough to
transform IN and NOT IN constructs into join-like queries automatically.
Right now, though, they're best rewritten into something else when
performance is important.
regards, tom lane
e looks a lot like something spitting up on a stray \r.
Dunno what your admin did to make the problem appear where you
hadn't had it before...
regards, tom lane
or '<>' for types '_text' and '_text'
> You will have to retype this query using an explicit cast
There are no comparison operators for array types ...
regards, tom lane
e looks a lot like something spitting up on a stray \r.
Dunno what your admin did to make the problem appear where you
hadn't had it before...
regards, tom lane
or '<>' for types '_text' and '_text'
> You will have to retype this query using an explicit cast
There are no comparison operators for array types ...
regards, tom lane
1000 locks held by one transaction :-(. So it chokes when you access
more than 1000 LOs in the same transaction.
Dunno about your other issue, but clearly your application is
failing to report whatever error message was returned when the
transaction was aborted...
regards, tom lane
Pierre Habraken <[EMAIL PROTECTED]> writes:
> It looks like if subqueries in from clause are not supported by
> PostgreSQL. Am I right ? If yes, are there any plans to provide this
> feature soon ?
Already there in current sources for 7.1 ...
regards, tom lane
Roberto Mello <[EMAIL PROTECTED]> writes:
> I was wondering if UNIONs in VIEWS will be supported too.
Already there for 7.1.
regards, tom lane
Forest Wilkinson <[EMAIL PROTECTED]> writes:
> I'd like to create an index on a column whose type is NUMERIC(12,2).
> There appears to be no default operator class for the numeric type.
Uh, what version are you using? Works fine for me in 7.0.2.
regards, tom lane
wondering if this might have some undesirable
> hidden side effects.
Yes, and they won't be too hidden either: it won't work :-(
Current sources check for that sort of type mismatch, but 6.5 failed to
do so.
regards, tom lane
ed by an improper shared memory or System V
> IPC semaphore configuration. Form more information
Most likely your kernel isn't set to allow shared memory blocks as
large as 8M.
regards, tom lane
--- the new column is added to the children too, as
it should be, but in an unexpected column position, which causes trouble
for pg_dump (a dump and reload will do the wrong thing). Perhaps what
you heard is a garbled report of that issue.
regards, tom lane
me;
> and I've got a parse error near UNION or SELECT depending of the
Current releases don't handle UNION in sub-selects. 7.1 will.
In the meantime, consider doing the UNION into a temp table and then
using that for the IN operator.
regards, tom lane
7;),1)
>> returns a "UNKNOWN expression type 501"
AFAICT it works in 7.0.* as well. Are you sure this was 7.0.2, and
not 6.5.something?
regards, tom lane
bind() to a port address that was just released by a previous
incarnation of the postmaster. But I haven't heard of such a
thing for shared memory or semaphores.
regards, tom lane
yours
is not.
The method for dealing with arguments passed to triggers is, um, arcane
--- I think you look in an implicitly declared array named TGARG, or
something like that. You don't receive them as normal function
arguments, anyway.
regards, tom lane
tty simplistic at the moment, and could be taken
a lot further if there were someone who wanted to work on it.
(hint hint)
regards, tom lane
x27;s started. But it hasn't got done yet. (Peter, would it be
easy to make GUC handle this? There'd need to be some way to cause
guc.c to do a putenv() ...)
regards, tom lane
ent locale in different backends. -- Unless someone puts an
> index on pg_database.datname. :-)
There already is an index on pg_group.groname, so I think we'd better be
conservative and require the same locale across the whole installation.
Hmm ... doesn't that mean that per-database encoding is a bogus concept
too!?
regards, tom lane
columns are,
for example.
regards, tom lane
, and I'll bet you get a more intelligent plan.
Current sources (7.1-to-be) are a little smarter than 7.0 about
cross-data-type joins, but they still don't get this case right.
I have a TODO item about that, but I dunno if it'll get done before
7.1 ...
regards, tom lane
s for which you compute the exact distance to
the target, sort, and limit. Not sure that you need to join to zips
at all if you do it this way.
regards, tom lane
1 - 100 of 2610 matches
Mail list logo
