Re: [GENERAL] Not able to understand how to write group by
unsubscribe pgsql-general@postgresql.org
Re: [GENERAL] Using varchar primary keys.
Natural keys are in user data space. Thus they are not guaranteed invariant
and therefore cannot serve as persistent identity.
Also natural keys have the considerable defect of being of different type and
arity per logical entity type. This means that very nice things like dynamic
relationships and persistent collections of references to multiple types of
things cannot be built. It also increases ORM and data cache complexity.
These are considerable weaknesses.
You cannot guess a uuid from say uuid.uuid4(). If you think you can I look
forward to seeing your proof.
- samantha
On Apr 2, 2013, at 11:16 AM, Merlin Moncure mmonc...@gmail.com wrote:
On Tue, Apr 2, 2013 at 10:34 AM, Joe Van Dyk j...@tanga.com wrote:
On Mon, Apr 1, 2013 at 1:11 PM, Tim Uckun timuc...@gmail.com wrote:
On Tue, Apr 2, 2013 at 8:35 AM, jesusthefrog jesusthef...@gmail.com
wrote:
On the topic of 'natural' versus 'synthetic' primary keys, I am generally
in the camp that an extra ID field won't cost you too much, and while one
may not need it for a simple table (i.e. id, name) one might add any number
of columns later, and you'll be glad to have it.
Nothing prevents you from adding more columns if you use varchar primary
keys.
My preferred method is to give every table an ID column of UUID type and
generate a UUID using the uuid-ossp contrib module. This also prevents
someone not familiar with the database design from using an ID somewhere
they should not (as is possible with natural PKs) or treating the ID as an
integer, not an identifier (as is all too common with serial integers).
This would be a concern if you had multi master writes . As far as I know
Postgres does not have a true multi master replication system so all the
writes have to happen on one server right?
As for UUIDs I use them sometimes but I tend to also use one serial column
because when I am communicating with people it makes it so much easier to
say dealer number X than dealer number SOME_HUGE_UUID. I often have to
talk to people about the data and UUIDs make it very difficult to
communicate with humans.
I've been wishing for a smaller uuid type for a while. Say you need to
assign a Order #. Customers might be referencing the number, so you don't
want it to be too long. But you don't want Order #'s to be guessable or have
the characters/digits be transposed accidently.
I've been using a unique text column with a default of random_characters(12)
CREATE OR REPLACE FUNCTION public.random_characters(length integer)
RETURNS text
LANGUAGE sql
STABLE
AS $function$
SELECT array_to_string(array((
SELECT SUBSTRING('abcdefghjkmnpqrstuvwxyz23456789'
FROM mod((random()*31)::int, 31)+1 FOR 1)
FROM generate_series(1, $1))),'');
$function$;
This seems to work ok. I don't allow 1's or l's or 0's or O's, as they can
easily be mistyped or misread.
This is pseudo random and can be guessed, which is maybe dangerous
depending on circumstance. For stronger random stream go to
pgcrypto.gen_random_bytes(). Also, now you have to worry about
collisions -- the whole point of uuid is to try and keep you from
having to deal with that.
My historical comments in this debate are noted. To summarize, I
strongly believe that natural keys are often (but not always) better.
merlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
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] Is there a way to bypass sql?
On Aug 9, 2010, at 11:57 AM, Merlin Moncure wrote: On Mon, Aug 9, 2010 at 2:39 PM, samantha sjatk...@mac.com wrote: I have been digging into NoSQL of late. For navigational queries it would be great if there was a way to bypass SQL and directly pull from an identifier for a record or arbitrary byte stream. Does postgresql directly support such ability? What is the closest that you could come? You can get pretty close, depending on how you define 'bypass'. For example, it is possible to send rich data structures back and forth between the client and the server without constructing a SQL text string. Those structures still have to be strongly typed in the server unless you want to stuff everything into a bytea (which btw I think is a terrible idea for most cases). Could you describe in more detail what you'd like to do and what (if any) inefficiencies or restrictions SQL is imposing that you would like to bypass? In many OO projects the majority of the work on persistent objects is navigational and inserts with relatively few updates. Queries are usually mainly for initial working set in many such systems and little else. When retrieving an object given a persistent oid it would be better if I didn't need to go through even a prepared statement and especially it would be better if I did not need to translate column values or do subqueries to either construct my OO language object or construct my OO cache entry. One thought is that I could in many cases store the cache entry format directly in a KV store and save a bit. There is also the interesting case of dynamic OO languages where technically the object fields do not have a defined type to start with. - samantha -- 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] Which CMS/Ecommerce/Shopping cart ?
This touches on a question I would love to be able to answer Why is MySQL so much more popular right now, especially in the OpenSource community? As a database I find its architecture with multiple underlying engines and other quirks to be rather dubious. Then there is the issue of commercial licenses and exactly when you must have those and what it will really cost. Yet it is pretty ubiquitous. How come? Why isn't postgresql more on developer's minds when they think of OS databases? Amazon cloud has great scalable MySQL support but apparently not postgreql. Why? Is there something about postgresql that is bugging all these people or what? - samantha On Jul 29, 2010, at 10:16 AM, Ivan Sergio Borgonovo wrote: On Thu, 29 Jul 2010 08:52:46 -0700 Joshua D. Drake j...@commandprompt.com wrote: The issue isn't Drupal. It is modules. There are a lot of popular modules that do not work with PostgreSQL (Lightbox for example). The google checkout module for Ubercart didn't work either until relatively recently. I'd say the opposite but I'll wait to test more D7. Core takes ages to agree on what should be done to fix bugs for Postgres without affecting even the feelings of MySQL developers. Modules may have more problems but fixing them is generally trivial and generally upstream is quick to integrate the fix. The problem for core is maintaining your patches till and if they fix the bug. I agree that PHP and MySQL are a perverse match. Still if he plans to deploy stuff as commodity software they are a necessary evil. The problem arise when you're in-between custom and RAD. Anyway more python/django based cms are flourishing... and given Django originally supported DB was Postgres... http://www.django-cms.org/ [1] Migration of Onion from Drupal/Mysql - Django/Postgresql is emblematic. [1] I think I could make a quick benchmark if possible on postgresql and mysql -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [TLM] Re: [GENERAL] How to insert on duplicate key?
On Dec 24, 2007, at 11:15 PM, Greg Smith wrote: This may be better because it isn't doing the query first. You may discover that you need to aggressively run one of the VACUUM processes (I'd guess regular and ANALYZE but not FULL) in order to keep performance steady as the number of records grows. Anytime you update a row, that becomes a dead row that's still taking up space, and if you do a lot of those they get in the way of finding the rows that are still live. Take a look at http://www.postgresql.org/docs/current/interactive/routine-vacuuming.html to get an idea of the process. Whoa. I am going to have to dig into the implementation. What is wrong with update in place, concurrency issues? The dead row presumably is no longer indexed, right? Since it is known to be dead is it automatically removed when there are no live transaction that reference or may reference it and its data page space marked available for new rows? If not, why not? I'm dredging my mind for stuff from my RDBMS implementation grad course a very long time ago.I would imagine that vacuuming often in a huge insert update would be a pretty poor performer depending on implementation. How is this implemented? Why would it be heavy IO if a list of pointers effectively is being kept to the dead rows to simply be added to the free list? What else is vacuum doing? Lookup implemented removal from indices, something else? - samantha ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] what is the date format in binary query results
Less than useful. I did read the thread last night. What am I missing? On Dec 12, 2007, at 12:06 AM, Tom Lane wrote: Samantha Atkins [EMAIL PROTECTED] writes: How can it be a simple 8 byte int or float and specify a timezone? It doesn't. Read the thread again. regards, tom lane - samantha Vote Ron Paul for President in 2008 -- Save Our Constitution! Go to RonPaul2008.com, and search Ron Paul on YouTube ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] what is the date format in binary query results
OK, I read it again. I don't see anything about how the timezone is specified for this type of column. On Dec 12, 2007, at 12:06 AM, Tom Lane wrote: Samantha Atkins [EMAIL PROTECTED] writes: How can it be a simple 8 byte int or float and specify a timezone? It doesn't. Read the thread again. regards, tom lane - samantha Vote Ron Paul for President in 2008 -- Save Our Constitution! Go to RonPaul2008.com, and search Ron Paul on YouTube ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] what is the date format in binary query results
This brings up a second question. How should I do byte order conversion for 8 byte ints? I can't use hton ntoh routines as they max out at 32 bits. Is there a better way? Also, are floating point numbers guaranteed uniform? If any one knows a a clean code example of binary binding of parameters and binary extraction of results for all major types in C against lippq that would be a very useful thing to have in the standard docs. - samantha On Nov 8, 2007, at 7:18 AM, Tom Lane wrote: Samantha Atkins [EMAIL PROTECTED] writes: What can I expect for a date format from a PGresult containing binary results? Specifically the Oid type is TIMESTAMPTZOID. It's either an int8 representing microseconds away from 2000-01-01 00:00:00 UTC, or a float8 representing seconds away from the same origin. regards, tom lane - samantha Vote Ron Paul for President in 2008 -- Save Our Constitution! Go to RonPaul2008.com, and search Ron Paul on YouTube ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] what is the date format in binary query results
How can it be a simple 8 byte int or float and specify a timezone? This is only a time interval from a fixed date/time. Where is the timezone part? On Nov 8, 2007, at 7:18 AM, Tom Lane wrote: Samantha Atkins [EMAIL PROTECTED] writes: What can I expect for a date format from a PGresult containing binary results? Specifically the Oid type is TIMESTAMPTZOID. It's either an int8 representing microseconds away from 2000-01-01 00:00:00 UTC, or a float8 representing seconds away from the same origin. regards, tom lane - samantha Vote Ron Paul for President in 2008 -- Save Our Constitution! Go to RonPaul2008.com, and search Ron Paul on YouTube ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] what is the date format in binary query results
What can I expect for a date format from a PGresult containing binary results? Specifically the Oid type is TIMESTAMPTZOID. In this case what does the PQgetvalue actually return? What does the char* point to? Thanks. - samantha
Re: [GENERAL] How to create primary key
On Nov 6, 2007, at 1:15 AM, Raymond O'Donnell wrote: On 06/11/2007 08:54, Reg Me Please wrote: What'd be the syntax to create a primary key on an already build table? ALTER TABLE test ADD CONSTRAINT test_pk PRIMARY KEY (f1); So, ALTER TABLE test ADD PRMARY KEY(f1 [, ... ] ) isn't enough? It appears in the examples. What more does the explicit test_pk do that wouldn't happen anyway? - samantha ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] for libpq which include defines the type oids
I am probably overlooking something but where exactly are these found for inclusion is libpq based programs? Poking around my installation doesn't make it obvious. - samantha ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] A few questions
First on prepared statements: 1) If I am using the libpq are prepared statements tied to a connection? In other words can I prepare the statement once and use it on multiple connections? 2) What is the logical scope of prepared statement names? Can I use the same name on different tables without conflict or is the scope database wide or something else? On indices: 3) same as 2 for index names. I think they are per table but it is worth asking. and last: 4) Is it generally better to have more tables in one database from a memory and performance point of view or divide into more databases if there is a logical division. The reason I ask is that I have a situation where one app is used by multiple different users each running their own copy. The app uses on the order of 30 tables. In some ways it would be convenient to have one big database and specialize the table names per user. But I am not sure that is most optimal. Is there a general answer to such a question? Thanks very much for any enlightenment on these questions. - samantha ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] A few questions
On Oct 29, 2007, at 10:14 AM, Richard Huxton wrote: Samantha Atkins wrote: First on prepared statements: 1) If I am using the libpq are prepared statements tied to a connection? In other words can I prepare the statement once and use it on multiple connections? Per session (connection). Temporary tables etc. are the same. 2) What is the logical scope of prepared statement names? Can I use the same name on different tables without conflict or is the scope database wide or something else? Per session. On indices: 3) same as 2 for index names. I think they are per table but it is worth asking. Per database (if you count the schema name). We don't have cross- table indexes, but the global naming allows it. and last: 4) Is it generally better to have more tables in one database from a memory and performance point of view or divide into more databases if there is a logical division. The reason I ask is that I have a situation where one app is used by multiple different users each running their own copy. The app uses on the order of 30 tables. In some ways it would be convenient to have one big database and specialize the table names per user. But I am not sure that is most optimal. Is there a general answer to such a question? Not really, but... 1. Do you treat them as separate logical entities? A set of tables per a user, yes. A app process is always for one and only one user. Do you want to backup and restore them separately? Not necessarily. Although the is a possibility of wanting separate per-user backups which would pretty much answer the question in this specific case. Is any information shared between them? Possible sharing of some common id numbers for common items. Although it is not essential the common items have the same serial number on different databases. What are the consequences of a user seeing other users' data? Little likelihood unless we expose database username/passwd. These are users not necessarily represented as postgresql database users. 2. Are you having performance issues with the most logical design? The first prototype has not yet been completed so no, not yet. :-) Can you solve it by adding some more RAM/Disk? ??? There is a desire to use as little ram/disk as possible for the application. I would be interested in what the overhead is for opening a second database. What are the maintenance issues with not having the most logical design? What do you consider the most logical, one database per user? - samantha ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
