~ubuntu-branches/ubuntu/precise/postgresql-9.1/precise-security

SELECT * FROM array_index_op_test WHERE t <@ '{AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}' ORDER BY seqno;

293

SELECT * FROM array_index_op_test WHERE t = '{AAAAAAAAAA646,A87088}' ORDER BY seqno;

294

SELECT * FROM array_index_op_test WHERE t = '{}' ORDER BY seqno;

295

SELECT * FROM array_index_op_test WHERE t @> '{}' ORDER BY seqno;

296

SELECT * FROM array_index_op_test WHERE t && '{}' ORDER BY seqno;

297

SELECT * FROM array_index_op_test WHERE t <@ '{}' ORDER BY seqno;

298

299

-- And try it with a multicolumn GIN index

300

301

DROP INDEX intarrayidx, textarrayidx;

302

303

CREATE INDEX botharrayidx ON array_index_op_test USING gin (i, t);

304

305

SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno;

306

SELECT * FROM array_index_op_test WHERE i && '{32}' ORDER BY seqno;

307

SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAA80240}' ORDER BY seqno;

308

SELECT * FROM array_index_op_test WHERE t && '{AAAAAAA80240}' ORDER BY seqno;

309

SELECT * FROM array_index_op_test WHERE i @> '{32}' AND t && '{AAAAAAA80240}' ORDER BY seqno;

310

SELECT * FROM array_index_op_test WHERE i && '{32}' AND t @> '{AAAAAAA80240}' ORDER BY seqno;

311

SELECT * FROM array_index_op_test WHERE t = '{}' ORDER BY seqno;

312

SELECT * FROM array_op_test WHERE i = '{NULL}' ORDER BY seqno;

313

SELECT * FROM array_op_test WHERE i <@ '{NULL}' ORDER BY seqno;

314

315

RESET enable_seqscan;

316

RESET enable_indexscan;

317

RESET enable_bitmapscan;

318

319

320

-- HASH

321

322

CREATE INDEX hash_i4_index ON hash_i4_heap USING hash (random int4_ops);

323

324

CREATE INDEX hash_name_index ON hash_name_heap USING hash (random name_ops);

325

326

CREATE INDEX hash_txt_index ON hash_txt_heap USING hash (random text_ops);

327

328

CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops);

329

330

-- CREATE INDEX hash_ovfl_index ON hash_ovfl_heap USING hash (x int4_ops);

331

332

333

334

-- Test functional index

335

336

CREATE TABLE func_index_heap (f1 text, f2 text);

337

CREATE UNIQUE INDEX func_index_index on func_index_heap (textcat(f1,f2));

338

339

INSERT INTO func_index_heap VALUES('ABC','DEF');

340

INSERT INTO func_index_heap VALUES('AB','CDEFG');

341

INSERT INTO func_index_heap VALUES('QWE','RTY');

342

-- this should fail because of unique index:

343

INSERT INTO func_index_heap VALUES('ABCD', 'EF');

344

-- but this shouldn't:

345

INSERT INTO func_index_heap VALUES('QWERTY');

346

347

348

349

-- Same test, expressional index

350

351

DROP TABLE func_index_heap;

352

CREATE TABLE func_index_heap (f1 text, f2 text);

353

CREATE UNIQUE INDEX func_index_index on func_index_heap ((f1 || f2) text_ops);

354

355

INSERT INTO func_index_heap VALUES('ABC','DEF');

356

INSERT INTO func_index_heap VALUES('AB','CDEFG');

357

INSERT INTO func_index_heap VALUES('QWE','RTY');

358

-- this should fail because of unique index:

359

INSERT INTO func_index_heap VALUES('ABCD', 'EF');

360

-- but this shouldn't:

361

INSERT INTO func_index_heap VALUES('QWERTY');

362

363

364

-- Also try building functional, expressional, and partial indexes on

365

-- tables that already contain data.

366

367

create unique index hash_f8_index_1 on hash_f8_heap(abs(random));

368

create unique index hash_f8_index_2 on hash_f8_heap((seqno + 1), random);

369

create unique index hash_f8_index_3 on hash_f8_heap(random) where seqno > 1000;

370

371

372

-- Try some concurrent index builds

373

374

-- Unfortunately this only tests about half the code paths because there are

375

-- no concurrent updates happening to the table at the same time.

376

377

CREATE TABLE concur_heap (f1 text, f2 text);

378

-- empty table

379

CREATE INDEX CONCURRENTLY concur_index1 ON concur_heap(f2,f1);

380

INSERT INTO concur_heap VALUES ('a','b');

381

INSERT INTO concur_heap VALUES ('b','b');

382

-- unique index

383

CREATE UNIQUE INDEX CONCURRENTLY concur_index2 ON concur_heap(f1);

384

-- check if constraint is set up properly to be enforced

385

INSERT INTO concur_heap VALUES ('b','x');

386

-- check if constraint is enforced properly at build time

387

CREATE UNIQUE INDEX CONCURRENTLY concur_index3 ON concur_heap(f2);

388

-- test that expression indexes and partial indexes work concurrently

389

CREATE INDEX CONCURRENTLY concur_index4 on concur_heap(f2) WHERE f1='a';

390

CREATE INDEX CONCURRENTLY concur_index5 on concur_heap(f2) WHERE f1='x';

391

-- here we also check that you can default the index name

392

CREATE INDEX CONCURRENTLY on concur_heap((f2||f1));

393

394

-- You can't do a concurrent index build in a transaction

395

BEGIN;

396

CREATE INDEX CONCURRENTLY concur_index7 ON concur_heap(f1);

397

COMMIT;

398

399

-- But you can do a regular index build in a transaction

400

BEGIN;

401

CREATE INDEX std_index on concur_heap(f2);

402

COMMIT;

403

404

-- check to make sure that the failed indexes were cleaned up properly and the

405

-- successful indexes are created properly. Notably that they do NOT have the

406

-- "invalid" flag set.

407

408

\d concur_heap

409

410

DROP TABLE concur_heap;

411

412

413

-- Test ADD CONSTRAINT USING INDEX

414

415

416

CREATE TABLE cwi_test( a int , b varchar(10), c char);

417

418

-- add some data so that all tests have something to work with.

419

420

INSERT INTO cwi_test VALUES(1, 2), (3, 4), (5, 6);

421

422

CREATE UNIQUE INDEX cwi_uniq_idx ON cwi_test(a , b);

423

ALTER TABLE cwi_test ADD primary key USING INDEX cwi_uniq_idx;

424

425

\d cwi_test

426

427

CREATE UNIQUE INDEX cwi_uniq2_idx ON cwi_test(b , a);

428

ALTER TABLE cwi_test DROP CONSTRAINT cwi_uniq_idx,

429

ADD CONSTRAINT cwi_replaced_pkey PRIMARY KEY

430

USING INDEX cwi_uniq2_idx;

431

432

\d cwi_test

433

434

DROP INDEX cwi_replaced_pkey; -- Should fail; a constraint depends on it

435

436

DROP TABLE cwi_test;

437

438

439

-- Tests for IS NULL/IS NOT NULL with b-tree indexes

440

441

442

SELECT unique1, unique2 INTO onek_with_null FROM onek;

443

INSERT INTO onek_with_null (unique1,unique2) VALUES (NULL, -1), (NULL, NULL);

444

CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2,unique1);

445

446

SET enable_seqscan = OFF;

447

SET enable_indexscan = ON;

448

SET enable_bitmapscan = ON;

449

450

SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;

451

SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL;

452

SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL;

453

SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL;

454

455

DROP INDEX onek_nulltest;

456

457

CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 desc,unique1);

458

459

SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;

460

SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL;

461

SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL;

462

SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL;

463

464

DROP INDEX onek_nulltest;

465

466

CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 desc nulls last,unique1);

467

468

SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;

469

SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL;

470

SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL;

471

SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL;

472

473

DROP INDEX onek_nulltest;

474

475

CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 nulls first,unique1);

476

477

SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;

478

SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL;

479

SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL;

480

SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL;

481

482

RESET enable_seqscan;

483

RESET enable_indexscan;

484

RESET enable_bitmapscan;

485

486

DROP TABLE onek_with_null;

Older »