1 package DBIx::Class::Storage::DBI::Pg;
6 use base qw/DBIx::Class::Storage::DBI/;
9 use Context::Preserve 'preserve_context';
10 use DBIx::Class::Carp;
13 use DBIx::Class::Storage::DBI::Pg::Sth;
15 __PACKAGE__->sql_limit_dialect ('LimitOffset');
16 __PACKAGE__->sql_quote_char ('"');
17 __PACKAGE__->datetime_parser_type ('DateTime::Format::Pg');
18 __PACKAGE__->_use_multicolumn_in (1);
20 __PACKAGE__->mk_group_accessors('simple' =>
23 our $DEFAULT_USE_PG_CURSORS=0;
24 our $DEFAULT_PG_CURSORS_PAGE_SIZE=1000;
26 sub _determine_supports_insert_returning {
27 return shift->_server_info->{normalized_dbms_version} >= 8.002
33 sub with_deferred_fk_checks {
34 my ($self, $sub) = @_;
36 my $txn_scope_guard = $self->txn_scope_guard;
38 $self->_do_query('SET CONSTRAINTS ALL DEFERRED');
40 my $sg = Scope::Guard->new(sub {
41 $self->_do_query('SET CONSTRAINTS ALL IMMEDIATE');
44 return preserve_context { $sub->() } after => sub { $txn_scope_guard->commit };
47 # only used when INSERT ... RETURNING is disabled
49 my ($self,$source,@cols) = @_;
53 my $col_info = $source->columns_info(\@cols);
56 my $seq = ( $col_info->{$col}{sequence} ||= $self->dbh_do('_dbh_get_autoinc_seq', $source, $col) )
57 or $self->throw_exception( sprintf(
58 'could not determine sequence for column %s.%s, please consider adding a schema-qualified sequence to its column info',
63 push @values, $self->_dbh->last_insert_id(undef, undef, undef, undef, {sequence => $seq});
70 my ($self, $function, $sequence) = @_;
72 $self->throw_exception('No sequence to fetch') unless $sequence;
74 my ($val) = $self->_get_dbh->selectrow_array(
75 sprintf ("select %s('%s')", $function, (ref $sequence eq 'SCALAR') ? $$sequence : $sequence)
81 sub _dbh_get_autoinc_seq {
82 my ($self, $dbh, $source, $col) = @_;
85 my $table = $source->name;
87 # deref table name if it needs it
89 if ref $table eq 'SCALAR';
91 # parse out schema name if present
92 if( $table =~ /^(.+)\.(.+)$/ ) {
93 ( $schema, $table ) = ( $1, $2 );
96 # get the column default using a Postgres-specific pg_catalog query
97 my $seq_expr = $self->_dbh_get_column_default( $dbh, $schema, $table, $col );
99 # if no default value is set on the column, or if we can't parse the
100 # default value as a sequence, throw.
101 unless ( defined $seq_expr and $seq_expr =~ /^nextval\(+'([^']+)'::(?:text|regclass)\)/i ) {
102 $seq_expr = '' unless defined $seq_expr;
103 $schema = "$schema." if defined $schema && length $schema;
104 $self->throw_exception( sprintf (
105 'no sequence found for %s%s.%s, check the RDBMS table definition or explicitly set the '.
106 "'sequence' for this column in %s",
107 $schema ? "$schema." : '',
110 $source->source_name,
117 # custom method for fetching column default, since column_info has a
118 # bug with older versions of DBD::Pg
119 sub _dbh_get_column_default {
120 my ( $self, $dbh, $schema, $table, $col ) = @_;
122 # Build and execute a query into the pg_catalog to find the Pg
123 # expression for the default value for this column in this table.
124 # If the table name is schema-qualified, query using that specific
127 # Otherwise, find the table in the standard Postgres way, using the
128 # search path. This is done with the pg_catalog.pg_table_is_visible
129 # function, which returns true if a given table is 'visible',
130 # meaning the first table of that name to be found in the search
133 # I *think* we can be assured that this query will always find the
134 # correct column according to standard Postgres semantics.
138 my $sqlmaker = $self->sql_maker;
139 local $sqlmaker->{bindtype} = 'normal';
141 my ($where, @bind) = $sqlmaker->where ({
142 'a.attnum' => {'>', 0},
143 'c.relname' => $table,
145 -not_bool => 'a.attisdropped',
146 (defined $schema && length $schema)
147 ? ( 'n.nspname' => $schema )
148 : ( -bool => \'pg_catalog.pg_table_is_visible(c.oid)' )
151 my ($seq_expr) = $dbh->selectrow_array(<<EOS,undef,@bind);
154 (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid)
155 FROM pg_catalog.pg_attrdef d
156 WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
157 FROM pg_catalog.pg_class c
158 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
159 JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid
172 sub bind_attribute_by_data_type {
173 my ($self,$data_type) = @_;
175 if ($self->_is_binary_lob_type($data_type)) {
176 # this is a hot-ish codepath, use an escape flag to minimize
177 # amount of function/method calls
178 # additionally version.pm is cock, and memleaks on multiple
180 # the flag is stored in the DBD namespace, so that Class::Unload
181 # will work (unlikely, but still)
182 unless ($DBD::Pg::__DBIC_DBD_VERSION_CHECK_DONE__) {
183 if ($self->_server_info->{normalized_dbms_version} >= 9.0) {
184 try { DBD::Pg->VERSION('2.17.2'); 1 } or carp (
185 __PACKAGE__.': BYTEA columns are known to not work on Pg >= 9.0 with DBD::Pg < 2.17.2'
188 elsif (not try { DBD::Pg->VERSION('2.9.2'); 1 } ) { carp (
189 __PACKAGE__.': DBD::Pg 2.9.2 or greater is strongly recommended for BYTEA column support'
192 $DBD::Pg::__DBIC_DBD_VERSION_CHECK_DONE__ = 1;
195 return { pg_type => DBD::Pg::PG_BYTEA() };
202 sub _exec_svp_begin {
203 my ($self, $name) = @_;
205 $self->_dbh->pg_savepoint($name);
208 sub _exec_svp_release {
209 my ($self, $name) = @_;
211 $self->_dbh->pg_release($name);
214 sub _exec_svp_rollback {
215 my ($self, $name) = @_;
217 $self->_dbh->pg_rollback_to($name);
220 sub deployment_statements {
222 my ($schema, $type, $version, $dir, $sqltargs, @rest) = @_;
227 ! exists $sqltargs->{producer_args}{postgres_version}
229 my $dver = $self->_server_info->{normalized_dbms_version}
231 $sqltargs->{producer_args}{postgres_version} = $dver;
234 $self->next::method($schema, $type, $version, $dir, $sqltargs, @rest);
240 $self->_pg_cursor_number(1);
241 return $self->SUPER::_populate_dbh();
244 sub _get_next_pg_cursor_number {
247 my $ret=$self->_pg_cursor_number||0;
248 $self->_pg_cursor_number($ret+1);
253 sub __get_tweak_value {
254 my ($self,$attrs,$slot,$default,$extra_test)=@_;
256 $extra_test||=sub{1};
258 if ( exists $attrs->{$slot}
259 && defined $attrs->{$slot}
260 && $extra_test->($attrs->{$slot})
262 return $attrs->{$slot};
264 my @info=@{$self->_dbi_connect_info};
266 && ref($info[-1]) eq 'HASH'
267 && exists $info[-1]->{$slot}
268 && defined $info[-1]->{$slot}
269 && $extra_test->($info[-1]->{$slot})
271 return $info[-1]->{$slot};
276 sub _should_use_pg_cursors {
277 my ($self,$attrs) = @_;
279 return $self->__get_tweak_value($attrs,'use_pg_cursors',$DEFAULT_USE_PG_CURSORS);
282 sub _get_pg_cursor_page_size {
283 my ($self,$attrs) = @_;
285 return $self->__get_tweak_value($attrs,'pg_cursors_page_size',$DEFAULT_PG_CURSORS_PAGE_SIZE,
286 sub { $_[0] =~ /^\d+$/ });
291 my ($ident, $select, $where, $attrs) = @_;
293 local $self->{_use_pg_cursors}=$self->_should_use_pg_cursors($attrs);
294 local $self->{_pg_cursor_page_size}=$self->_get_pg_cursor_page_size($attrs);
296 return $self->next::method(@_);
300 my ($self, $dbh, $sql) = @_;
302 if ($self->{_use_pg_cursors} && $sql =~ /^SELECT\b/i) {
303 return DBIx::Class::Storage::DBI::Pg::Sth
304 ->new($self,$dbh,$sql,$self->{_pg_cursor_page_size});
306 else { # short-circuit
307 return $self->next::method($dbh,$sql);
317 DBIx::Class::Storage::DBI::Pg - Automatic primary key class for PostgreSQL
321 # In your result (table) classes
322 use base 'DBIx::Class::Core';
323 __PACKAGE__->set_primary_key('id');
327 This class implements autoincrements for PostgreSQL.
329 =head1 POSTGRESQL SCHEMA SUPPORT
331 This driver supports multiple PostgreSQL schemas, with one caveat: for
332 performance reasons, data about the search path, sequence names, and
333 so forth is queried as needed and CACHED for subsequent uses.
335 For this reason, once your schema is instantiated, you should not
336 change the PostgreSQL schema search path for that schema's database
337 connection. If you do, Bad Things may happen.
339 You should do any necessary manipulation of the search path BEFORE
340 instantiating your schema object, or as part of the on_connect_do
341 option to connect(), for example:
343 my $schema = My::Schema->connect
346 [ 'SET search_path TO myschema, foo, public' ],
352 See L<DBIx::Class/CONTRIBUTORS>
356 You may distribute this code under the same terms as Perl itself.