root when they
don't need root powers is generally a bad idea.) -- Darren Duncan
Mike Christensen wrote:
Hi, I'm trying to require SSL for Postgres connections from certain
IPs.. This is on Postgres 9.0.
First, I've followed the directions at:
http://www.postgresql.org/docs
Other systems would be wise to adapt such a design also.
-- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
k over both kinds of DBMSs, although it emphasizes the ACID model.
-- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
tches besides full text search.)
-- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
nteractive/sql-altertable.html how you would
do what you asked.
The page http://wiki.postgresql.org/wiki/Alter_column_position also shows some
contrivances you could go through to get what you want, but it is better to just
not care about order in the first place.
-- Darren Duncan
--
Sent
mestamp,
pos point
);
This all being said, I would go with the other advice you mentioned and use a
bitstring or numeric type to represent the md5 rather than using text.
-- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
DESC
LIMIT (SELECT (count(*) / 10) AS selnum FROM mytbl)
There are probably other ways to do it also.
Maybe you want all items whose sales are in the top 90 percentile or something,
or maybe you want what generated the most profit, etc.
-- Darren Duncan
--
Sent via pgsql-general mailing
model. I demonstrate how it might be better done with my Muldis D language. --
Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Craig Ringer wrote:
On 20/10/10 13:12, Darren Duncan wrote:
Never mind JSON. You can fix the outer joins problem and other issues
simply by supporting relation-valued attributes, or in other words, row
field values that are rowsets.
You can for trees/forests yes. How would you handle more
rank() ...
see http://www.postgresql.org/docs/9.0/interactive/functions-window.html ... are
exactly what you want. -- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Use the Postgres window functions like rank(); this is what they're for.
http://www.postgresql.org/docs/8.4/interactive/queries-table-expressions.html#QUERIES-WINDOW
-- Darren Duncan
Alexander Farber wrote:
Hello,
I have a card game for each I'd like to introduce weekly tourna
',
'STOP_LOST','COMPLEX_INDEL','SPLICE_SITE');
... and then you could try using "ARRAY OF Consequence_Type" or some such.
-- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reece Hart wrote:
On Tue, Mar 8, 2011 at 5:52 PM, Darren Duncan wrote:
Try starting with an enum type to define the possible values:
CREATE TYPE Consequence_Type
AS ENUM ('ESSENTIAL_SPLICE_SITE','STOP_GAINED',
'STOP_LOST','COMPLEX_INDEL',&#x
e the proper way to do
non-deterministic things such as constraints or actions that depend on the
current system time, while CHECK is better for deterministic things such as
constraints a database should always have that only require looking at the
contents of the database itself.
-- Darren D
replacement.
In any event, QUEL was somewhat similar to SQL.
-- Darren Duncan
Rajasekhar Yakkali wrote:
"Following a great deal of discussion, I'm pleased to announce that the
PostgreSQL Core team has decided that the major theme for the 9.1
release, due in 2011, will be 'NoSQL'.
as dropped from Postgres. -- Darren Duncan
Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand
Darren Duncan 04/02/11 3:01 PM >>>
I was under the impression that QUEL was actually a good language in some ways,
and that it was more relational and better than SQL in s
nstraint failure.
Modify to taste.
In fact, this is my generally recommended method for doing any kind of bulk data
import, because its much easier to clean data using SQL than otherwise, and its
all very efficient resource-wise.
-- Darren Duncan
--
Sent via pgsql-general mailing list (pg
bundling batches of say a few thousand rows at a time into a single transaction.
Then pretend your newly populated temp table was your original source, but now
it is visible to SQL, and you can load your regular tables from the temp
table(s) much more nicely.
-- Darren Duncan
--
Sent via p
ake these deferrable, then why stop there?
-- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ay, their values should
never compare equal. So then, what you propose above would really just be
syntactic sugar for that. -- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
o what I want to do, but that looks more like abuse of the
system rather than using it as intended, though I could be wrong. Is there are
better solution than that or what should I be doing?
Thank you in advance.
-- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general
pointed to, but I was hoping for something less bulky.
Granted, I realize that sequence generators are basically small tables like this
behind the scenes, but is there any appreciable overhead of creating and
dropping a temporary table for every session?
-- Darren Duncan
On 2013.04.24 5:07 PM
n store/fetch should take care of that.
The temporary table approach wouldn't need casting in contrast.
Well, I can choose either then as the circumstances warrant.
Thanks for the feedback, I now consider my question to be answered.
-- Darren Duncan
--
Sent via pgsql-general mailing list
space from the base backup,
would that prevent proper backup recovery?
Although it would be nice if what you said would work, I read in a recent blog
post that losing any tablespace would prevent the database server from starting,
even if it was only for temporary things. -- Darren D
On 2013.04.30 7:14 PM, Ian Lawrence Barwick wrote:
2013/5/1 Yang Zhang :
On Tue, Apr 30, 2013 at 5:31 PM, Darren Duncan wrote:
On 2013.04.30 4:55 PM, Yang Zhang wrote:
I would intuit that it's fine, but I just want to make sure there are
no gotchas from a recovery point of view:
If I
thout
trouble. There would also be restrictions then, that certain things may not be
declared in such a tablespace, such as anything but temporary tables, or maybe
some other things. There is such a reasonable use case for this. A feature for
9.4 perhaps? -- Darren Duncan
--
Sent via
pes, then a binary/2-attribute/column relation/table
is the most direct analogy, so then use a TABLE(key text,value whatever) type.
From your example though, it looks like a ROW is definitely what you want.
-- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
are from each of the sequences, which SQL does quickly
and easily, and then you can eliminate any subsequences from the original whose
set of values don't match one of these common subsets, and only those would you
then have to compare for order.
-- Darren Duncan
On 2013.07.08 1:04 PM, R
e database
helps you avoid program bugs or corruption due to decisions made based on
incomplete or wrong database changes you make.
-- Darren Duncan
On 2013.08.15 9:14 AM, Perry Smith wrote:
The direct question is: what is the advantage of an immediate constraint?
My habit is to add constra
ncerns organize your schemas, where as schema is
like a library as a function is to a table ... or some other database object
like a stored function.
-- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresq
our table names.
-- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ation. The analogy is that Postgres is
the VM/language interpreter and the stored procedure is the script to run.
Now if said stored procedure has access to features that collectively let it be
computationally complete, including arbitrary user I/O, then you're done.
-- Darren Duncan
-
John R Pierce wrote:
On 05/21/11 10:41 PM, Darren Duncan wrote:
Well, if you can run a stored procedure automatically when Postgres
starts, that looks like a necessary step to being able to implement an
entire application inside Postgres.
Starting Postgres is running the application. The
John R Pierce wrote:
On 05/22/11 10:45 AM, Darren Duncan wrote:
Absolutely. But if the kind of stored procedures were supported that
can do anything a database client can do, including transaction
control statements, then the main program routine would typically be
one of those.
yes, but
the
DBMS to do too much at once.
If so, where is this documented? If not, how much work might it be to add this?
I'm looking for something enforced by the DBMS itself, not that an application
or bridge layer should do.
-- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-ge
Thank you, Steve,
That answers the part about statement times.
But what about timeouts for transactions as a whole, ensuring that any
transaction, once started, is ended one way or another within X time?
-- Darren Duncan
Steve Atkins wrote:
On May 28, 2011, at 7:55 PM, Darren Duncan wrote
r the data you
have at least, there would seem to be a relationship where X determines Y and Y
depends on X. Use an algorithm that does this looking for pairs. -- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
SQL DBMSs don't require BOOLEAN as the type of a WHERE expression.
Having real BOOLEAN is just one of the reasons I like Postgres the most.
-- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
g 8.4+ has named subqueries which handle a lot of cases
where temp tables would otherwise be used, I would certainly expect those to
work when you're dealing with a readonly database.
-- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make chang
Jeff Davis wrote:
On Thu, 2011-07-07 at 20:56 -0700, Darren Duncan wrote:
When you create a temporary table, PostgreSQL needs to add rows in
pg_class, pg_attribute, and probably other system catalogs. So there are
writes, which aren't possible in a read-only transaction. Hence the
error. A
Jeff Davis wrote:
On Thu, 2011-07-07 at 23:21 -0700, Darren Duncan wrote:
I think an even better way to support this is would be based on Postgres having
support for directly using multiple databases within the same SQL session at
once, as if namespaces were another level deep, the first level
h about it to make some useful contributions in the meantime. How much or
what I already know may not always come across well. If this bothers people
then I can make more of an effort to reduce my input until I have more solid
things to back them up.
-- Darren Duncan
--
Sent via pgsql-gener
ld also come up with some "relative name" syntax such as filesystems
support with their ../ and such, but that's further from standard SQL.
-- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Jeff Davis wrote:
On Fri, 2011-07-08 at 23:39 -0700, Darren Duncan wrote:
What if you used the context of the calling code and resolve in favor of
whatever match is closest to it? The problem is related to general-purpose
programming languages.
Basically start looking in the lexical context
oes not make the database
any less relational, because the above definition and any others still hold.
The "less relational" argument above is a red herring or distraction. One can
argue against namespace nesting just fine without saying that.
-- Darren Duncan
--
Sent via pgsql-
I will put my support for David Johnston's proposal, in principle, though minor
details of syntax could be changed if using "!" conflicts with something. --
Darren Duncan
David Johnston wrote:
On Mon, Jul 11, 2011 at 10:12 AM, Florian Pflug wrote:
On Jul11, 2011, at 07:08
in doing what you want. For example, if this is possible, have a
database-transaction-external task running which is LISTENing for your state
setting signal, and then it waits for an hour and then calls the function. Your
first function would send the NOTIFY signal. -- Darren Duncan
--
Sent
way in PL/PgSQL, assuming that PL/PgSQL
is a competent language, and then tweak to match what you actually can do.
-- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
John R Pierce wrote:
On 07/22/11 4:11 PM, Darren Duncan wrote:
Karl Nack wrote:
I've been following a few blogs
(http://database-programmer.blogspot.com/,
http://thehelsinkideclaration.blogspot.com/) that make a very compelling
argument, in my opinion, to move as much business/transact
ypes and/or domains?
Yes, you could. PL/PgSQL supports relation/rowset-typed arguments (declared as
"TABLE OF " or something like that).
Or alternately you could use temporary staging tables as quasi-arguments rather
than using an actual argument.
-- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
If you're using the bleeding edge anyway with Lion, why not use Postgres 9.0.4
with it, which is also on that page? Or at least see if it works where you got
this 8.4 error. I have nothing to suggest otherwise. -- Darren Duncan
Sean Moss-Pultz wrote:
Hi list
After installing Postg
that it is ideal for Postgres to be computationally complete in that
one *could* use it to implement a complete application. That isn't to say one
should do this as a matter of course, good to use appropriate tools for a job,
but that it should at least be possible if one wanted to. -- D
Chris Travers wrote:
On Mon, Aug 15, 2011 at 1:44 PM, Darren Duncan wrote:
I believe that it is ideal for Postgres to be computationally complete in
that one *could* use it to implement a complete application. That isn't to
say one should do this as a matter of course, good to use approp
at least be possible if one wanted
to. -- Darren Duncan
So who wants to fund the effort to create the necessary infrastructure to
display a programmer-defined user interface screen (think of the "Forms"
module in Microsoft Access)? Or are you expecting the end-user to open up
PgAdmin and typ
Chris Travers wrote:
On Mon, Aug 15, 2011 at 3:47 PM, Darren Duncan wrote:
I believe we basically have all the foundation already, with maybe
procedures executable outside transactions being the last major part.
Why is this desirable? Why is it more desirable than actually using
the listen
. You would be doing this
periodically anyway; a cluster is just your Postgres data files.
-- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
iles as they suggest changes to certain Mac
OS X settings so Pg runs better.
I hope this helps.
-- Darren Duncan
On Aug 27, 2011, at 3:48 PM, Darren Duncan wrote:
Kenneth McDonald wrote:
While trying to install 9.0.4, I ran into this: Problem running
post-install step. Installatio
7;11-07'
)
select
b.node
from a right join b on a.node=b.node
where a.accesses is null and b.channel in ('611 IVR', 'olam')
order by node
-- Darren Duncan
Joy Smith wrote:
with a as
(
select channel,node,accesses from storage where monthly = '11-06'
),
b
"where site_id ~ '...'" but
check the manual to be sure about regexp syntax. But "like" will probably do
you here.
-- Darren Duncan
Rich Shepard wrote:
I run this SELECT statement on a table:
select distinct(site_id) from chemistry order by site_id;
and in the
Darren Duncan wrote:
Try "like" by default, such as "where site_id like 'GW-22 %'". I added
the space between the 22 and the wildcard % so that the field containing
just 'GW-22' isn't also matched.
Sorry, I should have said "where site_id l
Based on your description, I suggest you might want to look at SQLite. It
provides a number of compile-time options where you can exclude various features
you don't want from the binary, when simply ignoring the extra features isn't
good enough. -- Darren Duncan
Albretch Mueller wr
, you need a persistent client script, but you don't need to invoke the
shell in the DBMS ... or use the untrusted version of PL/Perl if that's all it
was for.
-- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscrip
Michael Nolan wrote:
On Thu, May 31, 2012 at 2:23 PM, Darren Duncan wrote:
Michael Nolan wrote:
PL/pgSQL and PL/perlu are the only ones I use. I use PL/perlu
primarily to launch shell scripts from triggers, for example to
update an external website when a row in a
atement it can
have both meanings:
update mytbl
set foo = 3
where bar = 5;
Though procedural SQL also uses ":=" to mean assignment.
Thinking that "=" could only ever mean assignment is rather short-sighted; while
many programming languages do that, many more
tions you want to service, give the two schemas different
owners or do appropriate privilege granting in the database, to keep each
person/application to just the things they should see and nothing else. --
Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To
thout having to first undo or
skip the earlier portions. Also, rollback for everything is much more
deterministic. -- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
inserting your data into staging tables which lack
key constraints, and then you can use SQL to then either locate duplicates or
just copy the unique rows to the normal tables. I mean, ostensibly SQL is a
better tool for cleaning data than anything else right, usually, or reporting.
-- Dar
indexes and
slow updates.
A relevant question is whether these 5 tables all are self-similar or whether
each one is differently structured.
-- Darren Duncan
Menelaos PerdikeasSemantix wrote:
Let's say you have a father-child (or master-detail if you wish)
hierarchy of tables of not j
d be safeguards there to encourage users to not keep long-running
transactions or connections.
-- Darren Duncan
Levente Kovacs wrote:
Dear List,
I've been using PostgreSQL for quite while, but I'd need some direction on
how to handle an opened socket to the database in longer periods.
them (assuming those aren't human-chosen and provide clues to discerning
how they choose other newer passwords).
-- Darren Duncan
Shaun Thomas wrote:
Hey,
So, I've searched around through the archives, and it seems this has
come up a couple times in the past. But one scenario
INT, so you may have to study your schema
and its uses more to know what kind of data/type you actually have.
-- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Ian Harding wrote:
On Mon, Nov 5, 2012 at 7:46 PM, Lonni J Friedman wrote:
It says everything is happy as normal...
2012-11-05 16:22:41.200 PST - :LOG: invalid record length at BA6/6DCBA48
What does this log line mean? Is that "happy as normal"? -- Darren Duncan
--
Sent
rate words and some aren't, and you can do this pattern matching in an extra
select-list item which you then group by.
-- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
isn't documented anywhere. Is this because
it's common/insider knowledge? Can someone confirm?
It is documented, in
http://www.postgresql.org/docs/8.4/interactive/plpgsql-structure.html but I do
agree that it can be a challenge to find what section(s) of the manual has the
informati
staging table approach because the single SQL
statement afterwards is a concise declarational code saying what you actually
want to do, relative to a client-interfacing loop which is a more verbose
imperative version.
-- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general
something like this with postgresql ?
Yes, yes there is. GROUP BY is your friend and there are other friends too. --
Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
d the 3 urls it links to.
-- Darren Duncan
Kevin Jardine wrote:
I have a query structured like this:
SELECT stuff FROM
(SELECT more stuff FROM
table1
ORDER BY field1) AS q1
INNER JOIN table2 ON ( ... )
and have found that the INNER JOIN is ignoring the order set for q1.
The final results are
in a particular order. -- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
s a *bad* idea.
The fact that SQL lets you have a rowset with column names either duplicated or
missing is a horrible misfeature and one shouldn't rely on it.
-- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Darren Duncan wrote:
3. Suggestion, but it would be probably hard to implement: to make SQL
engine prefix each returned column with table alias. Of course it would
not be a default behavior, but it would be enabled by some session wide
setting.
# SELECT * FROM c1, c2 WHERE c1.id=c2.id;
c1.id
es ON test.people.id = filt_likes.ref
Try testing that.
-- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ated a database cluster there, then clone the
"data" dir from the old location to the new one while Postgres isn't running,
using the ordinary file system tools. I assume that would work, though I
haven't tried, so I don't know if a DB cluster contains absolute paths or
pplication, which then turns around and logs in as the temporary user in
order to do all the normal work of the person. This generated user only has the
privileges that the person needs. This approach seems to have security benefits
of some kinds. -- Darren Duncan
--
Sent via pgsql-general ma
Darren Duncan wrote:
A practice I like that I've seen done for a federal-government scale
database program is to have each person using the application to login
to the database using their own temporary database user. How it works
is that the database has a users table similar to as i
s the connection handle in
the same way afterwards. Its fine to have flags in the app so the app just
tries acceptable things, but privileges in the database are the only way to
actually be safe and resilient against accidental changes. -- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-ge
thing
to fix, so you both have a better design and it performs together on the DBMS
you have.
-- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
about space efficiency in the
database? BYTEA might help you. Or try declaring a DOMAIN over SMALLINT that
limits allowed values to the range of a byte. -- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.post
about space efficiency in
the database? BYTEA might help you. Or try declaring a DOMAIN over
SMALLINT that limits allowed values to the range of a byte. -- Darren Duncan
This is purely programmer convenience.
Basically, I want Npgsql to marshal the value as a .NET Byte type, if
I can find a
-engineering.tumblr.com/post/20541814340/keeping-instagram-up-with-over-a-million-new-users-in
Hopefully that will infect Facebook, which is MySQL otherwise, or at least
hopefully MySQL won't infect Instagram. -- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To
the schemas for each access like
this proposal. In fact, what you talk about sounds like an ideal case for
views; you could have 2 complete schemas, one per application, where each
corresponds to the other but one is lowercase and one is uppercase, and one is a
view. -- Darren Duncan
--
Sent
or
whether I should do something different for effective auditing instead? Or did
this even give you ideas for something to try yourself?
Or is what I said here not understandable?
Thank you in advance for your feedback.
-- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
91 matches
Mail list logo
